Power BI

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 →

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 …

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

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

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:

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…