Just wanted to share a method in Power Query that might also help you. Today I had a table where the column contained information about both the parent element and child element In the example the …

This article is the starting point of a series of articles that demonstrates how to navigate through tables inside a Tabular Model, from Microsoft Analysis Services Tabular, Power Pivot or Power BI using DAX

From time to time Huang Caiguang sends me some geeky M-code that turns out to be very useful (like this for example). Today it’s utilizing java script code for regular (…) Read more

The Challenge I love the idea behind Power Query (M)agic. These posts let Power Query users learn and share best practices (tips & tricks) that solve real problems. Query management comes up a lot. Wh…

A quick post more for my future reference. Hoping it helps some one out there! While I was working on a Power BI report with Commonwealth Games dataset, I came…

In the comments to my last blog post on how the order that you select columns can affect the output of certain calculations, both Bradley Sawler and John B. Thomas pointed out something very useful…

The draft of my book is almost ready (I even have few Excel formulas that are showing me, that I have already wrote 93% of the books, and a total of 359 draft pages so far). This weekend, after finishing 44 pages on M, I wanted to share with you, in this post, a 3-page …

Maybe this is obvious to more experienced Power Query users, but something I always point out when I’m training people up on Power Query is that the order that you select columns in the Power Query…

What is a “Navigation” step in Power Query? When using Power Query, you might’ve come across a step that reads “Navigation” and is usually automatically created for you by Power Query, but…what does it do? Well, if we look at the code that it was created for the Navigation step, it reads: = Source{[Item=”SalesTable”,Kind=”Table”]}[Data] It …

When you work with Power BI, you'll find that there are two languages to work with: DAX and M. Since most of the M query that you will regul...

Power Query and the M language is a bit of a pain, not a lot of people do it (Or they do, and they don’t write about it), and the knowledge base on MSDN is not real world example friendly. So…

My custom M function GetTableOfDateAndDateTime() makes it really easy to create tables of date or datetime values for your work with Power Query in Excel or Power BI Desktop.

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…