What the fact.bi - All about Power BI, DAX, M, Power Query

This post is also available in: Deutsch

WhatTheFact.BI YouTube channels (yes plural!) are here!

This post is also available in: Deutsch I am thrilled to share some big news that’s been in the works for a while now.As part of our commitment to provide you with the best Power BI resources and tutorials, we’re expanding our horizons by launching not one, but TWO YouTube channels! Why another YouTube channel? As you might have realized, there were not many articles in the last weeks. People even told me if I’m not doing anything now since I was awarded as MVP. But quite the opposite happened, I prepared the launch of two YouTube channel. While blogs have their advantage, as you can take your time to read the article, better explain and copy code and download the example file, videos have totally different advantages. Sometimes it’s just easier to explain something with your own words or a demonstration, instead of explaining where to click. For that reason, I have the feeling the combination of the blog, with a YouTube channel will give the best experience for our audience and teach you in the best possible way. And why two YouTube channels? The long term readers of my blog most likely realized, that I post all […]

Easy week over week comparison in Power BI with the new OFFSET function

This post is also available in: Deutsch In data analysis a common requirement is to compare your values of one week to the ones of the previous week. Such a week over week comparison gives important insights as you compare a bigger timeframe and can – without waiting for months – still see where the trend is going. How to handle the change of the year Usually you can just take the measure in a specific timeframe like the current week, for example week 28 of the current year 2023, and compare it to the previous week, in this case week 27 of 2023. However, such a comparison becomes challenging, as you have to handle the change of years. Somehow you have to manage that week 1 in 2023 suddenly compares to week 52 in 2022.And this is also the biggest challenge in week over week comparisons. You just cannot compare to the last week as if it would be a continuous number. New approach with window function OFFSET In the past there were a few approaches on how you can solve this issue, but all of them were kind of complex. However with the introduction of the new […]

Awarded as Microsoft MVP 2023 in Data Platform

This post is also available in: Deutsch A few days ago, something very special happened in my life. I received the legendary e-mail from Microsoft, that I was awarded Microsoft Most Valuable Professional or MVP for 2023!: What is a Microsoft MVP Before we dive into the details, let’s address the question that may be on your mind: What exactly is a Microsoft MVP? In order to answer that, I will quote the description from the official Microsoft MVP page: Microsoft Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. They have very deep knowledge of Microsoft products and services, while also being able to bring together diverse platforms, products and solutions, to solve real world problems. Why “Data Platform” and not “Power BI”? The MVP program is split into 14 categories. Each category contains certain related products that you can contribute to.As you can guess, the category “Data Platform” in uniting all data related technologies from Azure SQL, SSAS, Azure Synapse up to Power BI. And accordingly I was awarded in […]

Remove rows that exist in another table in Power Query – How to use an Anti-Join (Not-In-Join) for data cleansing in Power BI

This post is also available in: Deutsch When you’re preparing your data in Power Query, you might come to the point where you have to exclude rows in one table, that exist in another table. An example would be if in the sales table you want to exclude rows from business customers or rows from any kind of “black list”. In SQL this is called an anti-join. How you can use an anti-join in Power Query, I will explain in this article. What is a Anti-Join or a Not-In-Join? An anti-join, also known as a not-in-join, is a way to filter out rows in one table that match a specific condition in another table. It’s like taking the opposite of a regular join, where you combine matching rows from two tables. With an anti-join, you exclude rows from one table that have a matching value in another table, based on a specified column. This can be useful for data cleansing tasks, such as removing duplicates or filtering out unwanted data. Scenario for our example For our case, we imagine we have a fact table of multiple sales. We only want to analyze the sales to end customers, so we […]

One-step conditional value replacement in a column in Power Query

This post is also available in: Deutsch When you’re working a lot with Power Query, you will sooner or later have to replace values in a column, based on some conditions. Unfortunately, there is no step for that in the Power Query editor. Usually you have to add a conditional column and then remove the old column and rename the newly created one and move it to the place of the original column if you want to keep the order. This works perfectly, but if you work a lot with Power Query, you really want to get rid of the additional steps and do the transformation in one step. In this article, I will explain to you, how you can do a conditional value replacement in one step in a column in Power Query. Use a regular replacement as a placeholder In our case, I want to transform the product name to a category column and replace every product that contains “bike” with the category “Bike”. All the products that don’t contain “Bike” should be declared as “Accessory”. This scenario you might remember from the article a few weeks ago where I wanted to check if a column contains […]