Power Query

One of the most powerful features (and there are lots) that I’ve found about Power Query is the ability to hold an entire table in a single cell. Once I’ve got a table in a cell, then I can start performing some magic otherwise impossible in the pre-Modern Excel universe. You can download the .pbix and .xlsx formats

My posts from two weeks ago (see here and here) on using Process Monitor to troubleshoot the performance of Power Query queries made me wonder about another question: how does the performance of re…

Leading or trailing empty rows and columns during import Excel sheets make your queries more complex and slow than necessary. See how you can avoid this:

Using a custom function in Power Query M to convert an ip4 address into a numeric value, with the help of List.Accumulate

Intro Power Query (aka “Get and Transform” in Excel, aka the “Query Editor” in Power BI Desktop) is a powerful ETL tool, more powerful than you might expect from something native to Excel (also a native part of the free software Power BI Desktop). The example in this article is a bit anachronistic (I did it this way

A lot of things have happened in the last couple of days. I’m for the first time in my life in Seattle spending some quality time with people that I’ve never met in my life other than through the power of the internet. It’s a nice feeling to finally put a face to a name …

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 →

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

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…

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

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

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

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…

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

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

This post explores how you can split column contents into different rows using Carriage returns. The same approach works for other special characters.

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

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 …

Collection of cutom M functions for Power Query and Power BI

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

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

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

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 …

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.