Power BI

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…

What is a Custom Function in Power Query / Power BI, why we should use it whereas possible and what benefits it provides to our UX.

In today's blog post, I share the generic solutions for the Fill Down challenge that I posted here. As you recall, we had a table of backlog items with three levels of hierarchies (Scenario, Feature a…

Reading Prathy Kamasani’s excellent blog post on Sync Slicers the other week I had a revelation: in the post Prathy shows how it is possible to sync selections between two slicers that are based on di…

Business Problem: In a recent project with one of our clients, we were attempting to categorize website visit logs, based on the visitor’s landing page. The client had been maintaining a table of KeyPhrases that correspond with Categories. If the Visitor’s landing page contained the KeyPhrase, we wanted to match it

I was answering a question for a student in one of my online training courses this week. The question was, “Do you know of a way in power query to efficiently extract YYYY-MM from a Date colu…

Power Query has a lot of built in functions, but it doesn’t have a function that exactly matches the VLOOKUP in Excel – but by using the M language and other M functions we can certainly repl…