power query

Learn how to know your dataset better using column profiling, quality, and distribution features of Power Query in Power BI

I love using my data patterns to solve issues and in this post use one of my favourite recipes in order to label duplicates with Power Query.

We are super excited to announce that digital copies of Master Your Data for Excel and Power BI are now available for purchase!

This article continues to build on my COVID Case Fatality Rate report for Australia. Something happened with the global source data this week – in short, it was wrong. This is a global database and I am sure there are many people diligently trying to keep the data up to [...]Read More »

A quick note for anyone like me who spends too much time looking at the JSON exports from Performance Analyzer in Power BI Desktop: you may have noticed an event called Query Pending that isn&#8217…

was listening to this Excellent Podcast about Minerva, basically Airbnb went and built a Metrics Store which is a central tool that hold all Measures and dimensions, the idea is to have one source …

We are excited to announce that the Power Query forum is now available in both Power BI and Power Apps communities!

I’m pulling data from GitHub Codeowners into a Power BI report. All was dandy locally, but refresh failed on the PBI service with this:This dataset includes a dynamic data source. Since dynamic data sources aren’t

We often have our data spread out in different Excel workbooks and sheets. How do we combine all of them into one table? Let’s walk through an example. We have our data saved in a folder with three files: 2019.xlsx, 2020.xlsx, and 2021.xlsx. You can download the files here. Each file contains three sheets: AC, …

Tables in your Power BI reports don't need to be old-fashioned boring visuals. Check how you can create astonishing tables for your users!

This is not the first time I have shared this concept.  In my previous article I showed how it is possible to add a prefix to every column in a table. This article today is slightly different. Today I am removing text from multiple columns all at once using some [...]Read More »

This is not the first time I have shared this concept. In my previous article I showed how it is possible to add a prefix to every column in a table. This article today is slightly different. Today I am removing text from multiple columns all at once using some [...]

You may have noticed that a new dataflows connector was announced in the August 2021 release of Power BI Desktop, and that it now supports query folding between a dataset and a dataflow – whi…

Earlier this month I published a new and very practical Power BI app on Microsoft AppSource. The app audits Excel workbooks and detects changes and errors. To implement the app I needed to resolve key obstacles that are very common when you import Excel workbooks using Power Query. How do you import the data from […]

Why you need to build a custom connector to use Power BI with REST APIs that use OAuth2 authentication

Sample Table In the following scenario, I would like to insert a sum row for each 5 days as an example.   Method 1      let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLCsAgDATQu7iWkon1dxbx/tdoAqWQjrh7yiTjWklFcdlJOUEk7fyRGtUgxaQHuU1GkGrSgjR60yl5+HgEmhRk9/8k+N4z...

Usage scenarios: Sample data table:   #1. Check field if they existed English characters.(upper: 65 ~ 90, lower: 97 ~ 122)   Create a list of all English characters. List.Transform(List.Union({{65..90},{97..122}}),each Character.FromNumber(_))​ Add a custom column with function to compare each cha...

Learn how to create a report of column's value distribution in Power BI report using Power Query techniques and enhance data profiling.

There are some cases that we want to add a leading zero to a digit, such as showing 01 instead of 1, 02 instead of 2 and so on. We have two options to do this in Power BI, doing it in Power Query or doing it with DAX. Adding a Leading Zero in Power … Continue reading Quick Tips: Adding Leading Zero to Integer Values (Padding) with DAX and Power Query

My post earlier this year on enabling query folding when using SQL queries as a data source in Power Query provoked a lot of interest. This post adds one more useful detail: how to preserve the ori…

The script for a Power Query function that can be used easily to generate the column value distribution to be used in Power BI.

Learn how to change the data source type of a Power BI report dynamically using Power Query parameters in a step by step guide.

Creating a journal entry with Power Query. Learn how to create records, sum columns and combine datasets using Power Query.