Power Query

With Power Query inside of Excel or Power BI, you can replace values by using the ‘Replace values’ button, but there are other approaches when you need to replace multiples values at the same time like the following ones: Imke’s way Bulk Replace The reality is that there are multiple ways to do it, but …

Foreword from Imke: “Happy to publish my 2nd guest post here: I met Rafael Knuth in the Technet-forum where it was a joy to see how quickly he was set (…) Read more

In a previous post, I already blogged about case pitfalls in Power Query/M language. By default, comparisons are case-sensitive and that could be an issue when sorting list or tables but also when …

In a previous blog post, I explained how to handle the html entities when they were number-based such as À or ♣ ;. This post covers the other possibility to define an html entity, by …

Developing is relatively easy but maintaining is harder. A code relatively easy to maintain must have a good balance between expressiveness and compactness. In Power Query/M Language, passing a fun…

Huh ? I remember some years ago standing next to an IT guy at the biggest Brewing company in Canada of the time. He proudly showed me a new product he had created that allowed the President (down the …

I’ve downloaded a few M language based calendars, for use in Power BI recently and have been frustrated as they don’t quite meet my needs. So I’ve created my own. The main differe…

My daughters participated to the provincial championships of gymnastics during this winter, both qualified for the regional championships. Between the two events, I analyzed the results of the diff…

In a previous post, I explained how to parse a semi-structured flat file with the help of a range-join. When you just have a few thousand lines in your semi-structured flat files, this is working r…

In one of my projects, I have to deal with XML files. The structure of the XML is usually flat and I don’t need to expand columns to get access to the values I’m looking for. Loading th…

A long time ago someone – probably from the Power Query dev team – told me that adding primary keys to M tables could improve the performance of certain transformations in Power BI and Excel Power …

I worked through a brain-teaser on a consulting project today that I thought I’d share in case it was useful for someone else in the community. We needed to convert application user names into an …

CSV files are one of the most commonly used data sources in Power BI and Power Query/Get&Transform, and yet the documentation for the Csv.Document M function is very limited and in some cases i…

Adding allowed values to your custom M function adds high value to the user experience. This post shows you how to do that in the M language for Power Query and Power BI Desktop

In a comment on a previous post, a reader asked how you return a specific day of the next month from any given date. In other words, I've got a date of March 5 and I want to use Power Query to return April 10 in Excel (or Power BI). How do you do it?

When you are working with data in Excel or PowerBI the data often contains columns that is a combination of text and numbers. One example could be like this If you have this challenge you shouldn&…

Have you ever wanted to find out how many days you have between 2 dates? perhaps how many Mondays? how many Sundays? perhaps Saturdays and Sundays? Well, in this blog post I’ll show you how you can do that with Power Query inside of Excel and/or Power BI and how you can extend this to …

When you’re working with M code in Power BI or Power Query/Get&Transform you may want to include special characters such as carriage returns, line feeds or tabs inside a text value. To do this …

Imagine this scenario, you are a Doctor who has multiple appointments with patients on a daily basis. Sometimes, you need to schedule follow up appointments with your patients just to make sure that everything is going according to plan and basically do a check up. You record these appointments in a simple spreadsheet like the …

The scenario – source data sheets that are structured differently! Does Power Query sometimes seem too rigid? It’s great for defining a sequence of transfo

Create a fully dynamic Power BI table. This allows a user to select the Top N items from any number of column data fields. This table technique allows for wide flexibility in what your users are able to investigate within one table.

Two different options to combine multiple tables into one: 1) easy and obvious 2) works for dynamic selections and large number of tables

Although it's not much code, it's so convenient to have a generic equivalent to the DAX-SWITCH-function in M for the query editor in Power BI or Power Query

From the Water Cooler As a 'water cooler' of sorts for this community, we meet some amazing people. Doug Burke shares with us how easy Power Query makes it

Writing M in the Advanced Editor in Excel or Power BI can be a frustrating experience unless you’re the kind of masochist who loves writing code in Notepad. There are some options for writing M cod…