Usually you work with detail figures and create running or cumulative totals. But how to proceed the other way around when you just have cumulative figures and need to retrieve the details? Unravel cumulative totals like so:

Table.AlternateRows and List.Alternate are very useful function when it comes to extract alternating items. List.Alternate can be a bit tricky to understand, but Table.AlternateRows can help to grasp it.

Last year I blogged about how to use the Text.BetweenDelimiters() function to extract all the links from the href attributes in the source of a web page. The code was reasonably simple but there’s …

Recently I’ve distributed some techniques for partial matches or relative joins between tables using PowerQuery or the query editor in PowerBI. They are very flexible and powerful – yet slow. (…) Read…

This is a collection of tips how to improve performance of your M-queries. It will be updated continuously. Disable background refresh I turn this off in general. It slows down many (…) Read more

M's ability to perform recursive operations in Power Query and Power BI can come with a price on performance. Check out tricks on how to improve performance of List.Generate

I decided to extract the number of views from the website. The problem however is that the information is non-tabular data. But no worries, I used Power Query to extract the required data using a few of my tricks that I have developed and learnt over the last few years.

When you merge tables with distinct keys in Power Query you will get the same result than the VLOOKUP-function in Excel returns. But how to retrieve only the result of the first row, if the lookup-table has multiple rows with the same key?

Did you know that you can use Power Query as a data profiling tool? While the great majority of Power Query (M) functions are centered around accessing data sources and implementing transformations…

This is a step by step guide to getting started with LibPQ, an illustrated version of

Power Query Formula Language (also known as M language) is sometimes difficult to get your head around. This article explains how...

Power Query formula language (also known as M language) is a very capable yet not very flexible tool. It lacks some features taken for...

Another quick blog post today! I built a Power BI report over the long weekend using data from the Community Shield game on Sunday. I will use this report to demo some of the new features in Power BI …

While I was playing around with the new release (August 2018) of Power BI Desktop I noticed there was an undocumented change: similar to the OData improvements I blogged about here, there is a new opt…

You probably know that you can manually set data type for custom column created in Power Query with help of Table.AddColumn function (using 4th argument). Rick de Groot has recently published a goo…

What an overwhelming response to Power Query Challenge #2! We had 40 submissions, and some with multiple entries in a single submission. Plainly you all enjoyed this! Naturally, there were a couple of submissions that involved custom functions, and a … Continue reading →

One of the most powerful features (and there are lots) that I’ve found about Power Query is the ability to hold an entire table in a single cell. Once I’ve got a table in a cell, then I can start performing some magic otherwise impossible in the pre-Modern Excel universe. You can download the .pbix and .xlsx formats

My posts from two weeks ago (see here and here) on using Process Monitor to troubleshoot the performance of Power Query queries made me wonder about another question: how does the performance of re…

Leading or trailing empty rows and columns during import Excel sheets make your queries more complex and slow than necessary. See how you can avoid this:

Using a custom function in Power Query M to convert an ip4 address into a numeric value, with the help of List.Accumulate

Intro Power Query (aka “Get and Transform” in Excel, aka the “Query Editor” in Power BI Desktop) is a powerful ETL tool, more powerful than you might expect from something native to Excel (also a native part of the free software Power BI Desktop). The example in this article is a bit anachronistic (I did it this way

A lot of things have happened in the last couple of days. I’m for the first time in my life in Seattle spending some quality time with people that I’ve never met in my life other than through the power of the internet. It’s a nice feeling to finally put a face to a name …

I'm at the Microsoft Business Application Summit this week, so I thought I'd post another Power Query challenge, especially since our last one was so successful. For this Power Query challenge … Our business challenge here is that we are … Continue reading →

This post is really just a quick follow-on from my post earlier this week on using Process Monitor to troubleshoot Power Query performance issues with file-based data sources, which I suggest you r…