power query

It is very common to need to transform data from one “shape” to another “shape” before it can be used inside Power BI for analysis (although many beginners don’t reali…

Recently I found I needed to remove all the HTML tags from some text in Power Query. I searched and found a great – if complex – function from Marcel Beug here, but I realised that since that post was…

Power Query is the easiest way to connect, extract, transform and load data from a wide range of sources

By using dataflows you can separate refresh schedules. But probably you want to inform your end-users about the last successful refresh time as well. In this blog I'm explaining how to get one overvie…

An interesting challenge today come up while mentoring finance users in Power BI. Consider the following Excel layout. The user wants to unpivot the data by rotating the three header rows (Scenario Ty…

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…

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…