If you have been using dataflows, and you have the return value of a query as something which is not in the form of a table, such as a record, list, or value. then you have seen that the dataflow automatically converts them all to tables. There is an explanation about it, and a method Read more about Working with Records, Lists, and Values in Power BI Dataflows[…]
There are a couple of useful actions that help a lot in cleaning data; removing digits, or keeping digits and removing everything else. In this short blog article, I’ll explain a simple method to do that using Power Query. Sample Data My sample data table looks like this: Removing Digits I can easily get rid Read more about Remove Digits or Keep them in Power BI Using Power Query[…]
I have written previously provided an all-in-one script for the date dimension, and also wrote an article about the relative date slicer. However, sometimes you cannot use the relative date slicer (timezone issue can be one example), adding offset columns help to overcome that challenge, and still be able to do flexible relative filtering in Read more about Offset Columns for the Date Table: Flexibility in Relative Date Filtering for Power BI[…]
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[…]
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[…]
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[…]
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[…]
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[…]
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[…]
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[…]