power query

What is a Custom Function in Power Query / Power BI, why we should use it whereas possible and what benefits it provides to our UX.

In today's blog post, I share the generic solutions for the Fill Down challenge that I posted here. As you recall, we had a table of backlog items with three levels of hierarchies (Scenario, Feature a…

Reading Prathy Kamasani’s excellent blog post on Sync Slicers the other week I had a revelation: in the post Prathy shows how it is possible to sync selections between two slicers that are based on di…

Business Problem: In a recent project with one of our clients, we were attempting to categorize website visit logs, based on the visitor’s landing page. The client had been maintaining a table of KeyPhrases that correspond with Categories. If the Visitor’s landing page contained the KeyPhrase, we wanted to match it

I was answering a question for a student in one of my online training courses this week. The question was, “Do you know of a way in power query to efficiently extract YYYY-MM from a Date colu…

Power Query has a lot of built in functions, but it doesn’t have a function that exactly matches the VLOOKUP in Excel – but by using the M language and other M functions we can certainly repl…

Unstacking a column into a table made easy with an M-formula in Power Query and Power BI. No need to create a function if applied to all rows of a table.

How, why and when to create your own Parameter Tables and the options that you have when trying to create one in Power BI / Power Query.

In the first two parts of the series "Programming Loops in M" we dealt with the basic possibility of creating loop constructs in M for Power Query and Power BI with the help of recursive functions or the function List.Generate(). In the last part of the series I would like to introduce you to a function with which you are able to create the "For-Next"-loop type in M. The function List.Accumulate().

In the first part of the series we basically dealt with the possibility of programming loops similar to those of other programming languages. We looked at the concept of recursive functions in M for Power Query and Power BI. In this part I would like to show you an alternative way to overcome the performance issues of conventional recursive functions in M. The function List.Generate()...

Bulk-extract Power Query M-code from multiple Excel files at once: Made easy with just one M-function that is easy to apply.

Wil Reynolds asked an awesome question about how to use multiple URLs with the web from example Power BI feature. The magic is with Power Query functions and parameters. Patrick has you covered!

For a data warehouse professional, a shared dimension concept is always clear, and a star schema design always includes such type of entity. However, in the Power BI user's world, there are many users who are not coming from a data warehousing background. It is necessary to understand some of the concepts in order to…

This article describes how the Join types in Power Query work in a visually animated way that makes it easier for beginners to understand.

How to fix and understand the Formula.Firewall error and the Data Privacy Levels inside of Power Query and Power BI Desktop.

This Power BI blog post details how to update Column names using the Power Query editor where it uses Camel Case Notation in the names

In my last post I mentioned the Power Query engine’s persistent cache, which in some scenarios caches the data read from a data source when a query is refreshed. Another important nugget of informa…

I'll get to parameters for dataflows in a bit. To begin, let's talk about dataflows. Dataflows are Power Query queries that live outside of Power BI reports and can be refreshed on a schedule within Power BI service. They are available for Pro and Premium users. Adam Saxton (from Guy in a Cube) has a great intro

I had a requirement where I wanted to extract the data before and after the data as shown below. In order to do this, I created the following Custom Column with the syntax below. I did try and use the…

The basic principles of how to combine, stack, consolidate or append data using Power BI / Power Query for Excel for any data source.

Here’s yet another entry in the list of useful things I learned from Ehren von Lehe on the Power Query MSDN forum: Table.Buffer() does not buffer nested table, record or list values inside the cells o…

Previously, I have written about using Power Query transformations and functions to create an Exception report for Power BI. However, the method I mentioned there, was only doing the exception handlin…

In the first part of this series, you learned how to create an exception reporting for a column in a table using Power Query in Power BI. In the second part, you learned how to initiate that process f…

Re-assign your dataflows connections without rebuilding your dataflow? Pimp the dataflow connector in Power BI to make it easier to switch! Read all about it in my blog!

A practical example on how to combine flat files (like csv, txt and other extensions) using Power BI / Power Query using the From Folder experience.

Find out how to get even more interesting data from Microsoft Graph API by adding another function to the custom connector in Power BI.

Your query doesn't fold in Power Query or Power BI? Don't give up too soon and find out how to make it fold again. Query folding

When Power BI dataflows were officially released a few weeks ago there was a new setting for Premium capacities mentioned briefly in the announcement blog post: Container Size. The blog post only says…