A new splitter function has been discovered in Power Query (by Chris Webb and also others probably) which is not in the highlight notes of the February 2019 PBI release, but it is a very nice addit…

With the Power BI craze that kicked into high gear in 2017, it’s less common to be working in PowerPivot in Excel. However, during one of my recent remote consulting calls, one of our many clients needed some help with a solution created in PowerPivot in Excel. And I often find if ONE person needs help or has a

Working out how long a Power Query M query takes to run – the first step to troubleshooting refresh performance problems in Power BI or Excel – is something I have blogged about several times (see …

The February 2019 release of Power BI Desktop includes some new functionality not mentioned in the summary blog post, functionality that is already in the latest Excel 365 builds (thanks Ed Hansber…

Understanding the main principles of Parameters and Functions in Power Query and Power BI when it comes to Data Preparation and the M language.

While searching for a compact formula to retrieve the textual representations of a type in Power Query, I learned that a type comparison in Power Query will return unreliable results, as "Type equivalence is not defined in M". So I continue using my old function, which I'm sharing here as well.

A while ago I found myself wondering – as I often do about this kind of thing – about what the undocumented third parameter of the Excel.Workbook() M function (called delayTypes) actually does. I f…

In my Power BI courses I always recommend some books and sites that will help them learn DAX or M. Most of the time i'm presenting to new users to Power BI, and having to get them to move from the Exc…

The following steps show how to create a new column in a table using existing custom function code. Applicable to Power BI and Power Query in Excel.

Learn how to do a bulk find and replace in power query based on a list of values.

A quick hack today. Got this question from someone who needed to be able to update a report and show users that something had changed. This is easy when you...

Using error tables in Excel and Power BI to handle unsuccessful HTTP Requests.

A weekend challenge I hope you are enjoying your second weekend of 2019. I was helping a friend recently figuring out the following data challenge: You have a table of Backlog items with three levels of hierarchies (Scenario, Feature and Backlog Item). In each row, the source data includes the unique ID of the lowest …

Using Power Query to write out data is usually a very bad idea. But I found an exeption where it made a lot of sense to me: Writing data to GitHub to feed..

I have already blogged in great detail many times about Power BI/Power Query data privacy settings (see this series for example) but there’s always something new to learn. Recently I was asked a qu…

This blog post I will demonstrate how to change a DateTime column data type to Date dynamically. You might be wondering what I mean by dynamic, this means that I will not specify any actual column …

There are quite a few different common business scenarios where you get data in the form of a file on a regular basis. The 3 common patterns I use most often are. Each day/month/year you get a new…

Facebook Twitter LinkedIn If you haven’t read Part 1 of this series, I highly recommend that you read that prior to this post. In that previous post, we went over the Left Outer Join and some basics on how Joins / Merge operations work inside Power Query / Power BI where the position of the …

A few weeks ago I received a great tip from Simon Nuss about a Power Query feature relating to ODBC data sources that I hadn’t seen before (although it’s in Power BI Desktop and Excel 365, so it ca…

Export data from Power BI to csv -files is best done using Python. Check this article for hows and whys:

One of the best functions in Power Query (M) is for sure the function =#shared that inside the Query editor lists all the functions in M and in your current pbix file. Picture 1 Result of =#shared But…

I have known for a long time that data privacy checks can have an impact on the performance of data refresh in Power BI and Excel, but on a recent performance tuning engagement I had the chance to obs…

There are quite a few common business scenarios where you get data in the form of a file on a regular basis from some source system and you need to process these files into your Power BI reports. …

The order of the columns in a table in a Power BI dataset doesn’t matter all that much, especially because the Fields pane in Power BI Desktop ignores the original column order and lists the column…

Here I am showing some keyboard shortcuts in the Advanced Editor of Power Query Editor in Power BI Desktop, as well as show how to enable a mini map!

See a map of the Wisconsin 2018 midterm election results from data provided by John Johnson.

I was working with some external data where I had a list of Australia postal codes, the challenge is that there are multiple suburbs that are part of one postal code. Below I am going to show you h…

Recently I’ve been doing some more investigations into how data privacy settings work in Power BI. This is a subject I’ve blogged about in great detail already in a series of posts last year, but t…

In a comment on my blog post about international date and number formats and changing data types with the “using locale” option in Power Query/Power BI, Jan Karel Pieterse asked if there was any way t…

A common problem I see some people struggle with in Power BI is when a slicer contains a (Blank) record for some reason. The cause of the problem is not obvious and hence it is not clear how to fi…