Power BI Date or Calendar Table Best Method: DAX or Power Query?

So you want to create a date or calendar table in Power BI, and you search for ways to do that. Then you will find many different approaches for that. Some of the approaches are using Power Query, some are using DAX. Which method is the best? What is the actual difference between a date Read more about Power BI Date or Calendar Table Best Method: DAX or Power Query?[…]

Power Query Transformation Pattern: Convert Status Log Dates Table to Start and End Date Table in Power BI

I have previously explained how to analyze the data of a status log table and a from/to date table (like a subscription pattern). I also explained that in some scenarios a from/to table works better than a status log table. In this article and video, I’ll explain how you can transform or convert a status Read more about Power Query Transformation Pattern: Convert Status Log Dates Table to Start and End Date Table in Power BI[…]

Generate Year Month Day Combinations in Power BI Report using Power Query Date.ToText Function

A common requirement when reporting data with a Date axis is to be able to see the year and the month in a short form beside each other. Formats such as 2021 Jan, or Jan 2021, or even Jan 21, or 2021-01 are very popular. In Power BI, ideally, you should have a Date dimension Read more about Generate Year Month Day Combinations in Power BI Report using Power Query Date.ToText Function[…]

Add Leading Zeros to a Number in Power BI Using Power Query

There are a lot of situations where you need to add some leading zeros to a number. A common example of this is to create a month-year combination code using both month and year, and making sure the month is always two digits, regardless of being 1 or 12. There are many different ways of Read more about Add Leading Zeros to a Number in Power BI Using Power Query[…]

Book Overview: The Definitive Guide to Power Query in Power BI and Excel

Recently Leila and I published a series of two books under the name of The Definitive Guide to Power Query in Power BI and Excel. Both books are now available in ebook and paperback format on Amazon and Google Play Books. In this blog, I’ll explain some details about the book, the agenda, and who Read more about Book Overview: The Definitive Guide to Power Query in Power BI and Excel[…]

Change the Data Source TYPE of the Power BI Dynamically using Power Query Parameters

I have written a blog article and explained how Power Query parameters can be used to change the data source in Power BI. Another useful scenario of it is actually to change even the data source itself. For example, you may have two data sources with exactly the same data structure (columns), but different data. Read more about Change the Data Source TYPE of the Power BI Dynamically using Power Query Parameters[…]

Power Query Function: Column Value Distribution in Power BI

If you have a table and want to create a report of column value distribution of it in Power BI, This function helps you achieve it. I have explained previously in another blog and video, how to do this process step by step. In this article, I’ll share the code for a custom function that Read more about Power Query Function: Column Value Distribution in Power BI[…]

Creating Column Values Distribution Report in Power BI Using Power Query

I have written about profiling data and the importance of it in Power Query, I also explained how the profiling data can be prepared to create a report in Power BI for the user to understand the data better. And I explained how the error count of that can be added to the report. Another Read more about Creating Column Values Distribution Report in Power BI Using Power Query[…]

Why data preparation and transformation? Why Power Query?

Does Power Query requires training or reading a book, or even reading an article? I get this question a lot from those who started Power BI not long ago. Many users think that Power Query is just the get data from the source, and that’s it. Considering the many data source connectors in the Power Read more about Why data preparation and transformation? Why Power Query?[…]

Fuzzy Clustering in Power BI using Power Query: Finding similar values

I have written two articles about fuzzy operations in Power BI and Power Query so far;  fuzzy matching in Power BI, and fuzzy grouping. the methods above involve transforming the data with a fuzzy operation. However, sometimes you just want to know the similarity of values, or let’s say in other words; find the clusters. Read more about Fuzzy Clustering in Power BI using Power Query: Finding similar values[…]