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…

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…

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…

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

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…

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…

Creating Two-Tiered, Multi-Frequency Period Cycles Using List.Generate, Column References, and Internal/External Context in Power Query

One of the things you should know when working with PowerQuery is that you can get a list of all functions in M by adding a blank query and use the #shared expression to get all the functions. This…