My Journey with Power BI

This is the last post of my join series and it's about how to create a Cartesian product table in DAX and in Power Query. If you are wondering what is Cartesian product, google it :) I'm joking come back here :) Okay, serious mode on. First I'd like to share Wikipedia's definition and visual to describe it: ' In mathematics, specifically set theory, the Cartesian product of two sets A and B, denoted A × B, is the set of all ordered pairs (a, b) where a is in A and b is in B. In terms of set-builder notation, that is:' My definition for Cartesian product (in Power BI) is taking two different tables and combining them with all possible pairs of rows. To get this result in DAX we can use CROSSJOIN function. Microsoft's definition for CROSSJOIN is: "Returns a table that contains the Cartesian product of all rows from all tables in the arguments. The columns in the new table are all the columns in all the argument tables." The syntax is pretty simple:  ...

This post is about how to perform inner join in DAX. If you don't know what that is you, can access my previous post  here  which is about join types. Microsoft's definition for NATURALINNERJOIN is: " Performs an inner join of a table with another table. The tables are joined on common columns (by name) in the two tables. If the two tables have no common column names, an error is returned." And the syntax is: NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)   First of all the definition is wrong. I had two tables with no common column names (check the image below) and I got no error. This definition is really misleading because it makes you think that Power BI creates connection between columns by using common column names but that's not what happens. You need to have a relationship between 2 tables to perform inner join. This Microsoft definition definitely needs correction. These are my tables and below is the formula with the result table. As you...

First of all, why do we need to change the language of the calendar? If we're working on a report, which is going to have multiple languages for different users, changing the language of the calendar is a must and there are 2 ways to do that. The first one is the one that I don't recommend, changing the current file's regional settings from options and settings menu. This is the option to change the file's language and it's quite handy when we need to do that. But what if we only want to change the calendar's language, not the whole file. And what if we want to have multiple language options for our calendar. In that situation,  this menu is not going to be much of a help. So how can we do it? First, let's look at our date table code in M language. As you see, in lines 12 and 14. Date.ToText function is used to create Monthname and Dayname columns. Date.ToText function's name is quite self-explanatory. It converts date numbers to text. If you'd like ...

Before I get to the point, I'd like to give short information about transpose and unpivot options at Power Query Editor, in case you don't know. Transpose option converts rows to columns and columns to rows in the given table. On the other hand, the unpivot option converts horizontally stored data into a vertical format. After unpivoting, you get two new columns. They are called attribute and value. The attribute column consists of data of header of columns and the value column consists of whatever you have under header columns. But, to be able to transpose or unpivot successfully, there is one thing you should be aware of. And this post is about that one thing. In this table, I have countries and their GDP per capita values starting from 2000 until 2020. This table format is not ideal for analyzing the data because it has too many columns that actually should be rows. That's why I want to convert rows to columns and columns to rows by transposing. After I click on the tran...

Where do you go when you need to create a simple support table in Power BI? I usually prefer the Power Query Editor and use 'Enter Data' option. But what other options do we have? Would you like to find out? Then you've come to the right place! Other than Power Query Editor, we also have options in DAX to create a support table, and I am going to look into them one by one. Let's say I want to create a support table for color codes that I use in my report. First I am going to do it with Power Query Editor. After clicking 'Enter Data' at Power Query Editor, we get a new window where we can edit cells like in Excel. I use this option most of the time because it's easy to do some changes later, from source settings. So how do we do the same thing in DAX? To have exactly the same table by using DAX, we need to click on 'New Table' on Power BI Desktop and write the formula below:  Well, it's not "exactly the same". Power BI gave default colum...

The order of columns in Power BI might not seem important but there are some scenarios when locations of columns matter. For my latest project, I needed to append tables with DAX using "UNION" function at Power BI Desktop and I needed my date column at the beginning of the table, like the other table which I am going to append, but it was at the end like you can see below: I can almost hear you saying "That's easy. Go to the Query Editor, right-click on the column, and select Move To beginning." Honestly, I wouldn't be writing this if it had been worked. That step moved my column to the beginning of the table at Query Editor but not at Power BI desktop. After I close and apply, I saw my date column at the end of the table again. Hopefully, there is a very simple solution to this problem. First, we go back to our almighty Power Query Editor. On queries pane, we right-click on the table that has the date column, select Advanced Editor, and copy all the content...