Skip to main content

Transposing And Unpivoting Correctly

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 transpose option which is on the transform menu, this is the table I get.

Where is the data about years? Nowhere. It's gone. Power BI swallowed it. Why? To do a successful transpose operation, we need empty column headers on top of the table that Power BI can eat, which has no value for us. So I deleted the last step from the applied steps pane and clicked on this: 

This little fella here demote the headers of the table to the first row and creates column headers named Column1, Column2... After applying this step, I transposed again and this is the result I got: 

Now it works correctly. It didn't swallow anything that I need. So what about unpivoting? Let's say I'm still not happy with the result I got and I want to convert country columns to rows. After choosing country columns and clicking on unpivot columns option on the transform menu, this is what happens:

Country names were supposed to be the attribute column, not the value column. Why has this happened? It has happened because for unpivoting, Power BI takes the column headers and converts them to attributes. Meaning I should have country names as column headers. To do that, I deleted the last step from the applied steps pane and clicked on use first row as headers option from the transform menu. 

And what this fella does is, exactly the opposite of what use headers as first row does. It promotes the first row of the table into column headers. After clicking on this option, I choose the columns with country names and unpivot.


It worked correctly this time. After both transposing and unpivoting, Power BI has added new column headers named Column1, Column2... You can get rid of them again by clicking use first rows headers option. Long story short, if you're not getting the result you dreamt of after transposing or unpivoting, the column headers you have, or the column headers you don't have might be the reason. 

Labels:

Comments

Post a Comment

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...
Post a Comment
Read more

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...
Post a Comment
Read more

Customizing Themes With Advanced Elements in Power BI

Advanced Elements is not an intuitive description, that's why I'm going to show you what I'm talking about directly. For my recent project, I've decided to create my own theme for the report. When I was exploring Customize Theme tab, I have come up with the Advanced section. While text, visuals, page, and filter pane sections are self-explanatory, this advanced section seemed quite enigmatic to me. What are the first-level elements? Or the others? I think Microsoft agrees with me upon being enigmatic because the section comes with "Learn more" option: Click to  Learn More . But when I tried to learn more, it didn't make any sense to me, because I'm a visual person and those descriptions are quite hard to remember. That's why I have decided to be a good samaritan and visualize it, and then share it with you.  As you see, while the border is a third-level element, the shadow is a first-level element. Legend, x-axis, and y-axis are second-level elemen...
Post a Comment
Read more