Unpivoting, and then working with pivoted and unpivoted tables

[Anonymous]

Hello,

I am trying to turn the following table:

Date	Line	Front1	Front2	Front3	Front4	Front5	Front7
1/1/19	1	0	1	0	0	0	0
1/1/19	2	0	1	1	0	0	0
2/1/19	1	0	0	0	0	0	1

Into the following:

Date	Line	Value	Data
1/1/19	1	Front1	0
1/1/19	1	Front2	1
1/1/19	1	Front3	0
...	...	...	...
1/1/19	2	Front1	0
...	...	...	...
2/1/19	1	Front1	0
...	...	...	...

I have tried unpivoting but I cannot make it work. Any suggestions?

After that, I also need to work still with the original table because there's a few extra columns that I am interested to keep in that format. Is it possible?

Thank you very much.

[helassal · Accepted solution]

Hi @Anonymous ,

Unpivoting should work, please try the following:

1- First, create a reference from your base query in Power Query:

Now, you have the base query and a new query referencing the first query.

2- In the new query, select columns you wish to unpivot then right click and press "Unpivot only selected columns":

Now you should have the base query which can be used normally in the report and you have the 2nd query which references the first one and then unpivots the needed columns.

Sample Power BI file

Hope this helps.

[v-yuta-msft · Accepted solution]

@Anonymous ,

Click on column [Front1], [Front2], [Front3], [Front4], [Front5] and [Front7] and then click Unpivot, after that you will achieve two columns [attribute] and [value], rename them to [Value] and [Data].

 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[helassal · Accepted solution]

Hi @Anonymous ,

Unpivoting should work, please try the following:

1- First, create a reference from your base query in Power Query:

Now, you have the base query and a new query referencing the first query.

2- In the new query, select columns you wish to unpivot then right click and press "Unpivot only selected columns":

Now you should have the base query which can be used normally in the report and you have the 2nd query which references the first one and then unpivots the needed columns.

Sample Power BI file

Hope this helps.