Power Query

Understanding what and when / how to use the Data Profiling, Quality, Distribution features inside of Power BI Desktop and Power Query.

In part 1 of this series – which I strongly recommend you read before reading this post - I showed how removing columns from a table can make a dramatic improvement to the performance of certain trans…

The use of Table.ColumnsOfType is unfortunately not as intuitive as the function may seem. I try to change this with this post.

In an earlier post, I showed how to create a function that worked out if a date was a weekday or not. In this example, I've used that function to create a more complex function that adds a number of weekdays to a given date. In Power Query, go to the Edit Queries option and … Continue reading "Power BI: Creating an AddWeekdays function in Power Query M language"

Hi! Today I'm going to show you how to call a power query custom function from file in edit queries. You can now create a repository of custom functions and reuse them from there. Maybe in the near future we can have our m code more organized in our organization. This post will help us get throw thi...

I’ve just learned some really interesting new Power Query/M optimisation tricks! I’m going to write them up here as a series of worked examples rather than generalised patterns because I’m still tr…

As another section of the Power BI online book: from Rookie to Rockstar, I would like to get start working with Power Query. From my point of view learning through an example is the best way to learn …

Power Query can connect to a web page and get data from there, and I have explained it before in my article about movies data analysis. However, the default way of getting data from the web page doesn…

In the last post here, we walked through the steps to detect overlapping time periods and calculate their durations. We used Cartesian Product to match all the pairs of tasks of the same resource, and even used the M function Table.Buffer to accelerate the load of the table. In this article, we will learn how …

A few years ago I blogged about the Table.Profile M function and how you could use it to create a table of descriptive statistics for your data: https://blog.crossjoin.co.uk/2016/01/12/descriptive-sta…

I’ll admit that I’m a bit less excited about Excel dynamic arrays than I was when I last blogged about them. Don’t get me wrong: from a pure Excel point-of-view they are still very cool, but I’ve s…

Learn how to find overlapping time periods using #PowerQuery. In part 2 of this challenge you will learn step-by-step how to create the solution

It's been a long time coming, but I'm super excited that Power Query Intellisense and Highlighting features are finally here in Excel!

There are many scenarios that you might want to implement a conditional expression. When the conditional expression's logic is on a row-by-row basis, the best is doing it in Power Query rather than DA…

In Chapter 11 of my book, I introduced the Cartesian Product technique that enables you to combine two datasets in order to apply a calculation or filtering on each combination of paired records. In t…

While there is a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom (…) Read more

Power Query is now available inside of Excel for Mac. These are my first impressions and performance tests that I've done with this integration.

Hi Office International Community members, Did you ever want to measure wind speed from an anemometer? Or model the impact of the brain colliding with the skull? Even determine the electroconductivity of a solution? Excel’s Data Streamer Add-In supports capturing, visualizing and analysing real-t...

In Power BI Desktop, there is the option to display contents using a monospaced font, where each character has the same space. To do this, open your report and go to the Power Query Editor. Click the View tab, and you will see Monospaced: With this unchecked, we can see that our Id field is not aligned in our grid: On checking it, we can see that the field is … Continue reading Display Preview Contents using a Monospaced Font in Power BI

I’ve had many people tell me that they have a blog so they will have a place to look to find the information that they need, and answers to their own future questions. This isn’t typica…

Every respected computer scientist has heard about recursive functions (more details on Wiki: https://en.wikipedia.org/wiki/Recursion). There are many algorithms which are mentioned as example of such functions, i.e. Fibonacci sequence as the simplest example: Fib(0) = 0 Fib(1) = 1 For all integers ...

Make sure you apply your Power Query steps the right way. Don't let date columns ruin query folding in Power BI!

Whoa! I can’t believe it is summer and almost 4th of July! It’s been WAY too long since I’ve blogged – I’ve just been SUPER busy here at P3! But I can’t let my Pug Meme readers down It seems that Micr…

How, when and why to use recursive functions within Power BI / Power Query. Theoretical and practical examples

Thanks for attending my Hybrid Power BI session at the #MSBizAppsSummit 2019 in Atlanta. Thanks for getting there so early in the morning (8:15) and for your valuable questions and feedback! As I p…

This article shows what ascribed types are in M/ Power Query and why you should not use them except in very special cases.

Your Power BI model is fed by a data warehouse, and you'd like to make some reports, but now your Customer table has several entries for each customer instead of one. Then, you filter out the obsolete versions of customers, and you find that you're missing transactional data and you can't get Lifetime to Date (LTD)…

The Microsoft Business Application Summit was a blast. If you missed it, you can now watch all the Power BI session recordings here, or get a great snapshot for the major new features on the FourMoo blog. What did you love the most? For me, it was the release of PowerQuery.com and the amazing focus …

While writing my Microsoft Business Applications Summit session on Power Query-related reasons for Power BI dataset refresh failures (which you can watch online here for free, along with all the other…