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 […]
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 […]
This post is also available in: Deutsch If you work in analytics or business intelligence, you are probably already familiar with a date dimension, since almost every analysis has a reference to a date. But although nearly every data model has a date table, there is quite some confusion about the details. Should I use the column from the type date for the relationship or better the date as integer? Which of both is faster? And do I have to mark my date dimension as date table? Why sometimes my functions work without marking it as date table and sometimes they don’t?For a long time, I didn’t understand completely myself many details until Alberto Ferrari explained the for me missing details at the PASS Community Summit in November 2022. In this article, I try to break down this complex topic into an easy guide, in order to understand the details of date table relationships. The problem and scenario for our demo Whenever you work with a date related data model, at one point you want to use some time intelligence functions. In DAX, time intelligence functions are easy to work with, but you need a proper date table. A […]
This post is also available in: Deutsch Did you ever need to check if a certain text contains a specific value? While this may seem like a small topic, it’s one that I have come across multiple times in my own work, and I’m sure many others have as well.In this short article, we’ll explore how to handle this situation using DAX. With just a few lines of code, you’ll be able to easily identify whether your text contains the desired value or not. Example for our article Maybe I’ve worked too much in the past with AdventureWorks, but let’s imagine we are selling bikes and we have a product table. Our small example looks like this: For this column we want to check now, if it contains the text “bike” or if it doesn’t. The only function you need is CONTAINSSTRING In this case, we could do an easy approach and add a calculated column to categorize into “bikes” and “accessory”. For that, we only want to check if the value contains the word “bike”. The solution is quite easy, as the function CONTAINSSTRING is checking if a certain text contains a specific string. For our case, the […]
This post is also available in: Deutsch When you publish a dataset on the Power BI service, anyone with the proper access to it can make changes to the data model. Most of the times, you want people to cooperate and work together. But there are some cases, where you don’t want to allow others to override your dataset for some reason. This article explains how you change that, so that others won’t be able to override your dataset anymore. Tenant setting to disable override by others Actually, it is quite easy to disable others from making changes, as there is an option in the Power BI tenant settings. You only have to activate the “Block republish and disable package refresh”-setting: From then on, only the owner of the dataset can make changes to a dataset. When another user wants to override the dataset with the publish button in Power BI, he will receive the following error message: XMLA possibilities exclusively for yourself Another point I want to mention is how the XMLA endpoints react with this setting. As the owner of the dataset, everything stays the same. If you are not the owner, you won’t get any access […]
Deutsch
