Category: Power Query

Fibonacci Sequence: Understanding the Power Query Recursive Function for Power BI

Posted on by Reza Rad

Recursive functions in Power Query are not very popular, but sometimes very helpful when in need. In this post, I’ll explain what a recursive function is, how it works, and explain it through a famous recursive example of Fibonacci Sequence. Fibonacci Sequence Fibonacci sequence is one of the fundamental recursive operations in math, below are Read more about Fibonacci Sequence: Understanding the Power Query Recursive Function for Power BI[…]

Get Data from Multiple Excel Files with Different Sheet Names into Power BI

Posted on by Reza Rad

The ability to use Get Data from Folder in Power BI is a very useful option. However, if your sheet names are different in the list of Excel files, then you will face a problem. In this blog article, I’ll explain a method you can use that works regardless of the sheet names. Sample Dataset Read more about Get Data from Multiple Excel Files with Different Sheet Names into Power BI[…]

Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds Buckets

Posted on by Reza Rad

I have a Power Query script that I usually use in any Power BI solution that needs time-based analysis. However, I never had a chance to publish it here for everyone to use. So here it is. A friend’s question about a time table reminded me that it is good to publish this to the Read more about Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds Buckets[…]

Convert Flat List to Table in Power BI; Pivot Without a Set Key Column Using Power Query

Posted on by Reza Rad

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 Read more about Convert Flat List to Table in Power BI; Pivot Without a Set Key Column Using Power Query[…]

Cartesian Product in Power Query: Multiply All Sets of All Pairs in Power BI

Posted on by Reza Rad

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. Read more about Cartesian Product in Power Query: Multiply All Sets of All Pairs in Power BI[…]

Count of Unique Values (DistinctCount) in Power BI Through Power Query Group By Transformation

Posted on by Reza Rad

You can have a distinct count calculation in multiple places in Power BI, through DAX code, using the Visual’s aggregation on a field, or even in Power Query. If you are doing the distinct count in Power Query as part of a group by operation, however, the existing distinct count is for all columns in Read more about Count of Unique Values (DistinctCount) in Power BI Through Power Query Group By Transformation[…]

Customers Grouped by Count of Their Orders – Static Segmentation in Power BI

Posted on by Reza Rad

One of the interesting calculation challenges in BI systems is a segmentation or grouping on a numeric value, based on an occurrence of another value. As an example; You might want to know How many customers ordered once, twice, three times or more. The challenge is that the data in the transactional table is not Read more about Customers Grouped by Count of Their Orders – Static Segmentation in Power BI[…]

Be Careful When Merging on Text Fields in Power BI Using Power Query

Posted on by Reza Rad

Merging two tables in Power Query is one of the most useful transformations to combine data from different datasets, and also create a flatten data structure. However, if you use a text field for merge, then you might see some unexpected results. Here in this post, I share some important considerations if you are using Read more about Be Careful When Merging on Text Fields in Power BI Using Power Query[…]

Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically

Posted on by Reza Rad

Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. There is a very little but useful trick that Read more about Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically[…]

Analyse the JSON File with Power Query

Posted on by Leila Etaati

In the last Post, I will explain how to analyze a JSON file that has been generated in the Sentiment Analysis process . some explanation, this is a JSON file that contains the sentiment analysis for the comments one traveler put on the hotel website as below The suite was awesome. We did not have Read more about Analyse the JSON File with Power Query[…]