Power Query

Here’s a really common problem that occurs when combining data from multiple files, or indeed any ty...

Ever looked at a Power BI report visual and wondered what the logic was behind a calculation? In mos...

This post continues series of articles about M Function List.Zip ( first post, second post). Table.T...

Following on from my post last week about M functions that are only available in custom data extensi...

The Advanced Editor is where I LIVE and BREATHE when using Power Query, so welcome to my universe.

The November 2017 release of Power BI Desktop contains a new M function: Function.From. Here is the function’s description from the official documentation: Takes a unary function function and creates …

This is my second post about List.Zip. First one was about general usage of List.Zip, where I touched question of transforming column types in Power Query. Another scenario where List.Zip can be us…

There is one useful Power Query M function - List.Zip, but with poor documentation on MSDN. I hope, at some point, library of M functions will be available on Github like it is done for VBA. Power Que…

The environment concept is one of the most important concepts in M, while it is one of the least obvious ones. We'll show why it is important to understand.

The environment concept is one of the most important concepts in M, while it is one of the least obvious ones. We'll show why it is important to understand.

Ages ago I posted an article on unpivoting subcategories using Power Query. The technique is still valid, but what happens when you have a large data set?

Power-Query-Cheat-Sheet - Power Query Cheat Sheet

Here is the recording of my webinar at PASS Excel BI VC. If you missed it, you are encouraged to watch and follow the steps, with the materials that are available here to download. https://datachant.com/public/Webinar – Common Patterns.zip Hope you’ll find it useful. (Star Wars image credit here) Related

If you’re interested in cascading parameters and Power BI custom connectors is an option for your project, you should give a try to Navigation Tables. Which soon enough could make their way i…

If you want to extract the M-code from multiple Power BI pbix-files at once, the function in this blogpost will get you covered: Inclusive query- & stepname

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

Excel, of course, is quite capable of providing simple statistical summaries and analyses. However, there are occasions when bigger guns must be brought to bear on a statistical problem, and Excel

This post solves a tricky issue of removing offset duplicates or, in other words, removing items from a list that exist not only in a different column, but also on different rows. Problem History This data format is based on … Continue reading →

I don’t normally blog about Excel topics outside of Power Query and Power Pivot, but I think anyone who has ever done any serious work in Excel has found how difficult it is to concatenate more tha…

My last 2 blog posts have been about data compression – that theme continues today.  In December I blogged about the basics of compression and impacts that

Anyone who has tried to do any serious work with Power Pivot and Power Query will know about this problem: you use Power Query to load some tables into the Data Model in Excel 2013; you make some c…

I got a great question last month on how to implement a histogram in PowerPivot, similar to this great blog post by Tomislav Piasevoli for Multi Dimensional. I decided to solve the problem in Excel…

This post explores how we can use my Power Query parameter table technique to create dynamic database queries in Power Query.

If you want to collect your regularly loaded data without overwriting it or create a load log that writes the load activites with a

UPDATE – there’s now a better way to do this using copy/paste. See Here’s a tip that I found out about on the Power Query Technet forum that I thought was worth repeating. If you ever n…

by Matt Allington In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you

A year ago I wrote a post on loading the M code for a Power Query query from a text file using the Expression.Evaluate() function, but I admit that at the time I didn