Pivot or Unpivot table to separate columns

[Anonymous]

Hi all,

How is it that you pivot or unpivot columns?  so far i've succeded with trial and error, but for some reason i cant figure out how to transform this data properly.

Im looking to have units and revenue in separate columns.

Right now units and revenue is in same column with "number" indicating the value of either units or revenue.

[Maddy99 · Accepted solution]

PowerBI has a button that could help you easy.

1) go to query editor.

2) select the 2 columns (number and atribute) for you

3) click on "Pivot" in the transform menu.

4) choose "number" as your value. 

you can choose the default sum or do not aggegrate as an option. but the default would work to. 

i hope this would work for you.

[Anonymous]

Hi @Maddy99

One follow up question. Now that i units and revenue in their own column, i have duplicate independent categories. 

For example:

I have not only "product" and "country" in my real file, there I have like 8 unique categories/segments (don't know the right word for independent item). But i would like to only have 1 unique item and  then revenue and units on the same row, as shown in below picture. 

Can that be done?

[Maddy99]

is the caused by the pivot action?

can you set in the advanced editor the aggegration value to sum? this may help.

i was not able to recreate this problem . so i am not sure it this would work. because you have the 0 vales in your table

or you can do a goup. 

group all the other columns execpt revenue en unit. 

1) select all the other columns

2) click on group by

3) make to aggegrations with a sum. 1 for revenue and 1 for unit.

[Anonymous]

Sorry, that was a mistake.

I dont have the value 0 ,  it is just empty field, like this:

[Maddy99]

can that not be just the case?

for that specifick row there is not revenue available? 

in your print screen i dont see a row wich have both, a unit and a revenue. or the date or the product are always differnt.

[Anonymous]

thanks a lot for your answer.