Power Query

Improve performance of OData data sources in Power BI

Humans like turning a few things into many things. Investing $1 to get $10 . . . growing saplings into oaks . . . taking two rabbits and creating a fluffle. This post will show you how to do the exact…

The Power Query (M) Builder is a new tool for the XrmToolBox. It is designed to automate the basic Power Queries for getting data into Power BI from Dynamics 365. This includes selecting fields, re…

Automatically expand JSON no matter how deep they are nested? You can quickly see a glance what's in there. This function has you covered:

Recursive functions are a topic that I rarely come across. To give you some perspective, the instances that I’ve needed recursive functions can be counted with just 1 hand. This is one of those situations where a recursive function is probably the best way to go. Big thanks to NicoPer who posted this question on …

My recent post on showing the Top X with Ties inspired a discussion on ranking method choices. Oddly, as an accountant, I've never really been exposed to how many different methods exist. After a bit of research on 6 different common ranking methods, I had to see if I could reproduce them in Power Query.

Power query is awesome! Here are some tips and tricks to help you get the most out of this amazing data transformation tool.

Motivated by one of the latest videos from the MSFT guys behind Guy in a cube: where they talk about one of the hidden secrets behind Power Query / Power BI, I wanted to talk about another one of thos…

The function Json.FromValue provides a super-easy way to create POST-calls to web services that require JSON format in their body parameters.

In this Power BI Tutorial, Adam shows how you can easily work with JSON data within Power BI. Working with JSON data in Power BI Desktop is one of the best-kept secrets of Power BI.

Use email attachments as a data source for Power BI

Recently I found a PowerQuery gem, trick with Java/VB Script that allows to export data from Power Query to CSV without R / DAX Studio / SMS and Registration. However, related with risk. As everyth…

There are times where you need an efficient way to produce all unique combinations of your data. Recently a customer of mine used a report to track how the sales were doing. Their situation is as follows. The company reports several metrics for their business units. And not only that....

In this article I'll show how my classic method of Creating Dynamic Parameters in Power Query can be modified to display them as "Real" Parameters.

Learn how to create grouped serial numbers for your data in power query

Recently I worked on a challenge from Ken Puls, where I needed to figure out how to Rank With Ties. Ken had a set of data containing items and sales. And his idea was to plot the performance of the different items in a graph. As there were many items,...

Recently, I had to import a large-ish CSV file at around 1.5 million rows into Excel using Power Query. I used a From Text/CSV query but then I tried to export a new set of data with a longer timeline. In this post, we'll look at how we can convert a From Text/CSV query into a From Folder query.

I worked most of my career as an actuary at various insurance companies. Most of the time they had systems that were built before I was born, so I spent a good portion of time getting and transforming…

New Data Types in Excel will undoubtedly be THE talking point of the near future Excel evolution. That being said, they are also the reality in Office 365 Fast Insiders. That’s where they were recently introduced by Microsoft. This post will first explain what the new Data Types in Excel are and then we will […]

Power Query allows you to group data in any way you want. But what if you would like to reach into the grouped data in a later step? For example to find the person with the highest sales? This article will explain you just how to do that using the Table.Max function.

In this post, we’re going to talk about importing CSV files with power query! This is a guest post written by John MacDougall, an Excel MVP from Canada. John runs a famous Excel blog called How To Exc…

Data comes in countless forms. Sometimes structured with an SQL database, a table in Excel or several CVS files. The fun with Power Query starts when the delivered format is quite messy. This post foc…

About two years ago I wrote a blog post describing how the #table M function can be used to generate tables, but in that post I only covered the functionality I used regularly – namely using #table wi…

A quick walkthrough on how to import multiple SharePoint lists at once, like with the import from folder method on ordinary files.

In this week’s blog post, I created this Power Query Pattern, which I created to add in spaces for CamelCase text within a column. To get this to work for you, all that you do is need to make…

The results are in! Let’s find out which tool is better for the average Business Analyst based on the 3 scenarios that I showcased a few weeks ago here. I was able to get in touch with the Product Team from Microsoft’s Power Query and also the one from Trifacta’s Wrangler. I tried getting in …