power query

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…

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…

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

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!

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…

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. …

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…

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…

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

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…

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 …

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…

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 …

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…

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..

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 error tables in Excel and Power BI to handle unsuccessful HTTP Requests.

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...

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

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.

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…

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…

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.

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

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…

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 …

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

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…

Yet more evidence that Power Query is taking over the world: there’s a new Power Query data source for SQL Server Integration Services in the latest version of SSDT. Here’s the blog post announcing it…

Getting set up When I heard about custom data connectors for Power Query, I had assumed there would be a lot of work involved. While there is definitely quite a bit of work in implementing advanced fe…