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.

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.

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 …

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:

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…

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…

Improve performance of OData data sources in Power BI

A reader asked if we can number rows by group in Power Query, similar to the PARTITION function in SQL of course there is, we just need a little code.

Recently I have been working on a project where the solution should import a csv file exported from a SQL server. For some reason sometimes the data comes with a , (comma) as the decimal separator …

Table.RenameColumnsWithFunction lets you dynamically rename column names using a custom function. This is a very common use case for me:

M-Extensions are a very smart way to run you own M (or R)-function libraries in PowerBI: Those functions will appear as if they were native inbuilt-function

Combine data from multiple Excel worksheets in the same workbook using Power BI

Collection of cutom M functions for Power Query and Power BI

Sometimes for tables with too many columns, and also for databases with too many tables, you do need a bit of help to explore the data. As an example; you know that you are looking for a column named …

Referencing previous (or following) rows requires some manual steps and will soon become slow on large tables. The function Table.ReferenceDifferentRow has you covered:

Referencing previous (or following) rows requires some manual steps and will soon become slow on large tables. The function Table.ReferenceDifferentRow has you covered:

In this post we'll take a look at how to import an XML file into Excel and turn it into a more human readable table format. Excel has support for XML files and it's actually really easy to import and covert them to tables using Power Query

This is a continuation of this post In the data platform industry, we have been working with SQL for decades. It’s a powerful language and over many years, we’ve learned to work with it’s strength…

[ Related posts in this series: SQL, M or DAX: When Does it Matter? SQL, M or Dax? – part 2 SQL or M? – SSAS Partitions Using SQL Server Table-Valued Functions (UDFs) SQL or M? – SSAS Partitions in…

(2018-July-15) Your case is to create an Excel template with data extracted from a backend SQL Server database; it takes a few minutes and...

In this article, we look at how to use the M Language to manipulate text data when working with Power BI.

Troubleshooting Power Query performance issues in Power BI and Excel can be difficult because it’s a bit of a black box: there’s nothing in the UI to tell you what’s going on inside the Power Query en…

I have been working on a dataset which I will hopefully reveal soon, but part of that was that I was getting rate limited when making an API call. I found Chris Webb’s insightful blog post (U…

This post is really just a quick follow-on from my post earlier this week on using Process Monitor to troubleshoot Power Query performance issues with file-based data sources, which I suggest you r…

I'm at the Microsoft Business Application Summit this week, so I thought I'd post another Power Query challenge, especially since our last one was so successful. For this Power Query challenge … Our business challenge here is that we are … Continue reading →