Power Query

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…

Have you wondered how to find the native query in Power BI Desktop after query folding does its thing? Do you even know what query folding is? In this video, Adam looks at how you can find the query t…

Power BI offers fantastic ways to integrate data from online sources. Often it is useful to just use parts of that and extend your own data set with details from an online API. Here -as promised in ou…

Have you ever wanted to do a specific type of filter that might be too specific to do with just the regular filter drop down or even the advanced filter operations in Excel? Let me give you an example…

In Chapter 12 of my book, I published detailed walkthroughs that cover Translator Text API, Sentiment Analysis, Key Phrase extraction and the detection of languages using Cognitive Services. As I worked on the chapter, I realized that it will make sense to prepare a template that will streamline all of these APIs together to address …

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

I am often working on datasets where there is more than one condition for a conditional column. And whilst the GUI based Conditional column is really good, it currently does not have the capability…

Split text by any (whole) string instead of any character (within a string) like in the native Text.SplitAny -function with my modified new version:

A few days ago, I ran into a problem where I had to filter a product table by a dynamically set of columns – so in one solution it could be filtering by field x and y and in other solutions it had …

Have you ever had a similar situation where a REALLY important value of the file is in either a header or a footer section? Let me give you a clear example with the following file: In the image above you’ll see that we have 2 values in the header section right under the logo of …

The more advanced Power Query/M developers among you will know about query folding, the way that the Power Query engine pushes as much of the heavy-lifting of a query back to a data source. You may al…

One of the most common issues faced when calling web services in M is that the the easiest way of doing so – creating a function that calls the web service, then calling the function once per row in a…

The big news in the world of Excel right now is the introduction of dynamic arrays. They’re only available in the Office 365 click-to-run version of Excel and, at the time of writing, only available t…

This is the second bonus exercise for my book, Collect, Combine, and Transform Data using Power Query in Excel and Power BI. In Chapter 5 you learn how to preserve context from titles in the worksheet grid after you combine multiple tables, as illustrated here: In this bonus exercise you will learn how to address a …

This is the first post of a series of bonus exercises that will accompany my book, Collect, Combine, and Transform Data using Power Query in Excel and Power BI. The topics of these bonus exercises wil…

The M-Language for Power Query and Power BI is a functional language that is foreign to the classic loop constructions of other languages. Instead, functional programming languages use recursion to repeat expressions. In the first part of this series I show you how to implement recursive functions in M and we take a look at advantages, disadvantages and alternatives of recursive functions.

With the recent announcement of dataflows in the Power BI Service, I see more people will be looking to better understand and leverage dataflows by using the M language which is available in Power …

The idea of creating a Power BI custom connector for the Power BI REST API is not a new one: Miguel Escobar wrote one earlier this year (see here for the source code and documentation). However the Po…

See a transparent history of changes to Power Query M functions by using GitHub.

While the native function Text.BetweenDelimiters will only return one occurrence of a string found between 2 delimiters, my new function will fetch all of them.

In this video, we look at the new fuzzy match / merging option within the October 2018 version of Power BI Desktop. This feature is incredibly powerful when it comes to data matching and can be adjusted to meet your needs.