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[…]

Power BI Modeling: Change the Status Log Table to a From-To Table

It is common that you face a status table in your data analysis scenarios. A status table normally has a column for the status of the items and the date of status change. This type of table, although informative, can be hard for analyzing data. In this article and video, I’ll explain why it is Read more about Power BI Modeling: Change the Status Log Table to a From-To Table[…]

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[…]

Generate Year and Month Combinations in Power BI Report using DAX

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 and Month Combinations in Power BI Report using DAX[…]

Power BI HR Pattern: DAX Measures for the Last Position to Date: Last Status to Date

One of the common patterns in Power BI is to have a table with different statuses throughout time and then wanting to get the latest status up to a selected date. A simple example of that is to have a table with position(role) changes of employees and then wanting to see their latest position based Read more about Power BI HR Pattern: DAX Measures for the Last Position to Date: Last Status to Date[…]

What a Power BI Hierarchy Is, and How to Use it?

Power BI hierarchies are a useful structure for drill-down and data exploration in reports. A hierarchy can be created simply and can add great value for you as a developer and end-users for better data analysis. In this article and video, I’ll explain what a Power BI hierarchy is and how to create it. Video Read more about What a Power BI Hierarchy Is, and How to Use it?[…]

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[…]

Market Basket Analysis in R Part 3

About a couple of years ago, I wrote two articles about what is association rules and how we can have them in Power BI.In this post, I am going to put some more enhancements and show you how with data that is not cleaned how you can create Market Basket Analysis. these enhancements are about Read more about Market Basket Analysis in R Part 3[…]

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[…]