power query

The following is a pretty common scenario that I’ve seen with flat files exported from legacy systems, but it also applies to multiple other scenarios. It uses the M language inside of Power BI Desktop / Power Query, but you can follow along without knowing that much about it. The scenario: a table that contains […]

One new feature that was introduced in the December 2019 release of Power BI Desktop, but which wasn’t mentioned in the release blog post, is the “Diagnose Step” button on the Tools tab of the Powe…

The following is a pretty common scenario that I’ve seen with flat files exported from legacy systems, but it also applies to multiple other scenarios. It uses the M language inside of Power BI Desktop / Power Query, but you can follow along without knowing that much about it. The scenario: a table that contains […]

Static Tables created via “Enter Data” has non-readable code. This post provides a solution to that.

I have written another blog article previously about how to set up your Power BI to get data from a file in OneDrive for Business folder without needing gateway. That was a popular blog article, but I got questions about what if I want to get data from a folder in OneDrive for business but…

Detecting outliers and anomalies using the three-sigma rule of thumb in Power BI with no code

Recently I received a question on how to Unfill in Power Query - remove all duplicate instances of a value, replacing them with null

If you want to create a row-number statically as a pre-calculation in Power BI, then Power Query has a very simple way of doing it; Add Index Column. However, the Index column creates the row number regardless of any grouping or categorization. sometimes you want to take that into account too. There is a little…

There are times that you need to get the list of FOLDERS and not just files. Of course folder itself (without considering files in it), doesn't contain data to be used for a report. However, sometimes, even the folder name might contain some useful information. The Get Data From Folder option in Power BI will…

Learn how to use Power Automate to detect issues with your Power Query data refreshes in this hands on tutorial from PowerBI.Tips.

Tracing and diagnostics for Power Query can get you crucial information to troubleshoot problems in Power BI Desktop. Find out how!

I have written another blog article previously about how to set up your Power BI to get data from a file in OneDrive for Business folder without needing gateway. That was a popular blog article, but I got questions about what if I want to get data from a folder in OneDrive for business but…

I have written multiple blog posts about how to create a date dimension using Power Query, however, the purpose of those blog posts was to teach you how to do it yourself, learn the process and also build the date dimension that you can use. Sometimes, however, you just want to create a date dimension…

Last week’s post showed an M function that took Power Query diagnostics data and formatted in a way that made it suitable for visualisation in a Power BI Decomposition Tree visual. This is gr…

Working with APIs in Power BI? This article provides some suggestions on how to work efficiently with API calls in Power BI.

Working with APIs in Power BI? This article provides some suggestions on how to work efficiently with API calls in Power BI.

This post and the video walk-through demonstrates how the order of steps added to a query can make a big performance difference and drastically effect the number of steps generated by the designer.

Test your JavaScript, CSS, HTML or CoffeeScript online with JSFiddle code editor.

Calculate net working days like in Excel with my new Date.Networkdays function for Power Query. For holidays, simply refefrence column of a calendar table

A quick point: while the Power Query Query Diagnostics functionality is relatively new, it’s based on Power BI/Power Query trace logging that has been around for a while. I’ve just real…

The scenario I am explaining today is a very specific use case, However, I have seen examples similar to this happens very often. It happens that you get a text or Excel file with values written in a flat list structure, and that needs to be changed to a table format. Usually, this process can…

Sometimes, you need to create a multiplication of all sets of all pairs from two different data tables in Power BI. This action is different from Merge (Join) because there is no matching key columns and no way to relate the two tables together. However, you want to create a multiplication as a flatten table.…