Unpivot my data - Excel / PowerQuery / SQL

[p4nny]

Hi

I have summarized data in the following format:

	2021	2022	2023
admin	1	2	2

I would like to convert this so it is presented as:

admin	2021
admin	2022
admin	2022
admin	2023
admin	2023

I've tried PowerPivot and the Unpivot option. That doesn't seem to show in this format.

Thanks in advance for any help / guidance

 

[sandy666]

use Power Query

1. select admin column
2. unpivot other columns
3. delete column with numeric values

btw. admin column should have header

 

[p4nny]

Hi

thanks for your response,

1) I've highlighted the column with "admin"
2) unpivoted on other columns


I wanted the Attribute column repeated as per the value as above. Have I misunderstood?

Thanks again

title	Attribute	Value
admin	2021	1​
admin	2022	2​
admin	2023	2​

 

[sandy666]

try

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Admin", type text}, {"2021", Int64.Type}, {"2022", Int64.Type}, {"2023", Int64.Type}}),
    UOC = Table.UnpivotOtherColumns(Type, {"Admin"}, "Attribute", "Value"),
    TSC = Table.SelectColumns(Table.ExpandListColumn(Table.AddColumn(UOC, "Div", each {1..[Value]}), "Div"),{"Admin", "Attribute"})
in
    TSC

Admin	2021	2022	2023		Admin	Attribute
admin	1	2	2		admin	2021
					admin	2022
					admin	2022
					admin	2023
					admin	2023

 

[p4nny]

Thanks! This works a treat! Much appreciated

 

[sandy666]

You are welcome
Glad you are HAPPY
Thanks for the feedback