Power Query

An interesting challenge today come up while mentoring finance users in Power BI. Consider the following Excel layout. The user wants to unpivot the data by rotating the three header rows (Scenario Ty…

By using dataflows you can separate refresh schedules. But probably you want to inform your end-users about the last successful refresh time as well. In this blog I'm explaining how to get one overvie…

Power Query is the easiest way to connect, extract, transform and load data from a wide range of sources

Recently I found I needed to remove all the HTML tags from some text in Power Query. I searched and found a great – if complex – function from Marcel Beug here, but I realised that since that post was…

It is very common to need to transform data from one “shape” to another “shape” before it can be used inside Power BI for analysis (although many beginners don’t reali…

Creating Two-Tiered, Multi-Frequency Period Cycles Using List.Generate, Column References, and Internal/External Context in Power Query

One of the things you should know when working with PowerQuery is that you can get a list of all functions in M by adding a blank query and use the #shared expression to get all the functions. This…

Most of the time, you would like to know how the data quality looks like in your tables. Although, this is a process that you mainly check at the time of data preparation, having such report for end u…

This Power BI blog post uses Power Query to show to get an output from a table as a value, which can then be used as part of another M Power Query

Understanding the if statement inside of Power BI / Power Query and how Conditional Columns can be created through the UI and a Custom Column.

As a footnote to my previous post on storing large images in Power BI, I thought all you M nerds out there would be interested to read about a strange performance quirk I noticed while writing the cod…

In this post, we will look at using the Power Query (M) Builder in XrmToolBox, developed by Mohamed Rashid and Ulrik “CRM Chart Guy” Carlsson. This tool is a great way to quickly help build Dynamics 3…

How to change the data source of your queries (file path, folder path, or any other data source parameters) using Power BI / Power Query with the Data Source Settings.

This blog post shows how you can use Text.ReplaceAll in Power Query to replace multiple words at once.

Jason Thomas and Gerhard Brueckl have both blogged on the subject of storing images as text inside a Power BI dataset: http://sqljason.com/2018/01/embedding-images-in-power-bi-using-base64.html https:…

What is the maximum length of a text value in Power BI? It turns out that this is a more complex question than you might think! The maximum length of a text value that the Power Query engine can lo…

This is the full definition fo the new Table.ContainsAnywhere function in Power Query. It covers optoins for search lists, case sensitivity, partial match..

You should master records in M/ Power Query, in order to become an M ninja. This post shows you all the basic concepts behind this value type.

Learn How to create a template to Reconcile Accounts Using Power Query for Excel. [with Video, Learn and Earn Activity and Workbook to download]

Mike Allen Systems - Doing Smarter Things With IT -

Two years ago I wrote an article on how to export data from Power BI Desktop to an SQL database. Now there is another way which Power Query can use directly in SSIS.

Mike Allen Systems - Doing Smarter Things With IT -

Another week, another event done. This time it was SQL Saturday Helsinki, where I did two sessions. First session was the Power BI cheat sheet explained, and second Storytelling & interactive r…

June 11-12 are getting near, and it is this time of the year to start getting excited. Microsoft Business Applications Summit is getting close. Do you plan to attend it? I will be glad to meet you in person in one of my sessions that will focus on my Power Query book and the development …

Adam walks through column profiling within the Power BI Desktop. This includes the latest updates with the general availability (GA) within the April 2019 Power BI Desktop release. Demo files: https://guyinacu.be/demofiles

DMVs (Dynamic Management Views) are, as the Analysis Services documentation states, “queries that return information about model objects, server operations, and server health”. They’re also available …

I had an interesting question from one of my book readers and I thought it would make a good blog article. There are many ways of solving this problem, and I blogged about solving a similar problem…

You should master lists in M/ Power Query, in order to become an M ninja. This post shows you all the basic concepts behind this value type.

The most optimal approach when trying to combine, append, consolidate, stack data using Power BI / Power Query for any data source.

Search through all of your tables' columns with my new Table.ContainsAnywhere function instead of being limited to single columns that you have to specify.