Skip to main content

Merging columns with different values from two tables in Power BI


Case - I have two columns for country code and there are some mutual values and that is totally fine, but in some cases those columns have individual values for themselves without the other one having it, which had to be taken care when merging.

Why this has happened

 When I was playing with data as usual I got a scenario where I had two country code columns from two different tables.


How did I end up here

 These columns were extracted from two different tables where one is about offices and the other one is about other places. At one point I had to group by country code and see how many offices per each country and how many other places per each country and above tables are what I got.


                     As you can see there is “AE” in both tables  but “TH” is only there in CountryCode/Other table likewise “TW” is only there in CountryCode/Office table.

 What I wanted to do next

  Now I want these tables to be merged and have only one column for CountryCode where it should contain all the values from both tables.


My way of doing it

 Since I have two tables first I wanted to merge them and make it one.

 As you can see above I have selected the Merge queries option which is in the top right Home task bar of the query editor. If you don’t understand tap the below task bar and see it for your self.


Do you see it now? Well then I selected "BuildingsOther" which would have been much nicer if named the other way and merged that with "BuildingsOffice" and got the following table as result because of the join kind i used "Full Outer". 

Full outer join is the best option for you to choose if keeping all the rows is mandatory like the case I'm dealing now.



Now I have two country code columns where there are some null values in both because of the problem I mentioned in the very beginning, if you don’t remember the problem scroll to the top.  


There may be many ways to solve this but my way of doing is by using conditional column as shown above.

You can select conditional column from add column task bar of the query editor. In the below screen shot conditional column is there try to find it yourself.


Conditional column is an easy way for you to use an “if condition” even though you don’t have any idea about it. So good huh, I wanted to name my new column “AllCountryCode” because I’m going to get all the values in both columns to one single column.

You can see the attributes that I have chosen for the “if condition” in the above image and the logic I wanted to imply is so and it is below for you.

“Get the values from “CountryCode” if you see a null then ignore it and put the corresponding value you find in the “BuildingsOfficeCountryCode” otherwise use the value in the “CountryCode” to fill the “AllCountryCode” column” 
If you don’t understand this I highly recommend to read it again. 

This is what I got for implementing that logic.

               

Now I have all the country codes in one single column as I wanted it to be J

Secret

 If you have null values for the CountryCode in the individual tables that I have shown in the very beginning this might go wrong when i was implemeting the conditional column but in my case I know there is no null values so my way of doing worked for me.

Blank and null are two different things. In the “if condition” I wanted to use “BuildingsOffice.CountryCode” only when “CountryCode” is null but this won't happen if it is a blank so in that case replacing all blanks by null will solve this like I have shown below.





                                                                
Labels:

Comments

Post a Comment

Popular posts from this blog

Choosing color scheme for Power BI reports

Choosing beautiful colors for an attention seeking Power BI report is always challenging. Sometimes we may have an important message but it might go unseen if proper color scheme is not used, so it is important to create reports which are informational and visually appealing. In this blog our main objective is to get an idea about selecting appropriate color scheme and tools that can be beneficial to do this. What is a color scheme? As you all know not all colors look great together and you can tell stories by just having only two colors, have a look at the following image, there in the middle you can see a circle with same color and same dimension, only thing that changes is the back ground, but depending on the back ground the way you see those circles are totally different. Some small circles appear softer, some appear sharper, and some are brighter also you might notice movements or depth effect too. It is important to choose colors wisely to serve the purp...
Post a Comment
Read more

Ragged hierarchy in Power BI

Case- I have a data set which has to be visualized in a ragged hierarchy. What is ragged hierarchy A ragged hierarchy is a user defined hierarchy with an uneven number of levels.   You can see the above table which has 5 levels of hierarchy. In the first level we only have “World” as a category then moving forward to the next level we have two categories  namely “UK” and “USA” likewise the level of hierarchy is drilling down up to five levels with an uneven amount of categories. Creating a hierarchy in Power BI In the above image  you can notice that I have right clicked the “level 1” column which has to be the first level of our hierarchy, Once it is right clicked there you will notice “New hierarchy” as the second option then you will have to click that in order to create a new one. As you can notice in the above image once I have created a new hierarchy then a new column is created named “Level 1 Hierarchy” in that I have d...
3 comments
Read more

The new “Sync Slicers” is it better than Drill through Feature in Power BI ?

Power BI is a visualization tool for intelligence analysis. In my personal carrier I have been working with this tool for over a period of half-year and the tool is very user-friendly with engaging UI and an easier learning curve. For the past few months, the arising question is to have a better filtering mechanism throughout the reports with multiple pages in power BI, so that people can precisely view what they want. This has been earlier enabled by the introduction of the drill-through feature which requires some hassle for the end-user to navigate to a specific point which involves multiple key presses and it is not a healthy experience in an end-user perspective.  The newly introduced sync slicers seem to be a better alternative than the drill through but how far does it fit and is it really better than the drill through? Let’s dig more on to this, and it's you to decide. First of all, I would like to say what does the sync slicers exactly does. “Sync slicer...
Post a Comment
Read more