power query

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 …

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

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

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:

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…

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

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 →

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…

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…

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 …

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

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

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

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…

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?

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.

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

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

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…

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 …

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.

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:

Have you ever wanted to extract information from a web page to use in a calculation, or as a reference for something else you may be working on? I have Use Microsoft Excel Power Query to scrape engineering data from web sites that contain unstructured data to be used in your next calculation or report.

Level: Beginners Power Query is a product that allows business users to extract data from any source, cleanse and transform that data, and then load the modified data to a new location, typically i…