Power Query

I was looking at the list of M functions supported in custom connectors and not in Power BI Desktop (using the technique I blogged about here) in the latest version of the Power Query SDK when I came …

Time to finish up our exploration of primitive, single-value holding types! We’ll learn about handling true and false, explore the oxymoron of null (a value that represents the absence of a value) and…

Power Query, an Excel add-in from the Power BI family, allows you to extract and manipulate data from various sources. When you define transformations on the data, it is possible that those transformations are sent back to the source, in order to improve

See, how the new M editor in Power BI Desktop helps you to write M code easily, with Intellisense, Keyword highlighting, parameter hints and help texts.

Inspired by Ruth Pozuelo from Curbal latest video - https://www.youtube.com/watch?v=jlogBrcYZwc&t=2s where she shows an excellent tip on how to change column headers to upper or proper case – And the …

PowerBI doesn't have a connector for Elasticsearch, but by using the Web connector with the Elasticsearch REST API you can easily combine the two

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…

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.

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 →

In Power Query it is easy to end up with many applied steps. To prevent this, you can define the Data Type at the same moment as you add a custom column.