Power BI

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 …

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

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

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…

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

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…

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

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

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…

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

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

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.

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

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…

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…

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

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

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…

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…

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 …

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…

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 …

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…

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

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"

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

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…

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

Patrick is going bananas with these different ways that Column from Examples can help you shape your data without writing code in Power BI. This feature really helps you to be efficient! Download sample Want to download the sample report? Hit the download button below.