Unpivot my data - Excel / PowerQuery / SQL

p4nny

Board Regular
Joined
Jan 13, 2015
Messages
246
Hi

I have summarized data in the following format:
202120222023
admin122

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

admin2021
admin2022
admin2022
admin2023
admin2023

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

Thanks in advance for any help / guidance
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
use Power Query
  1. select admin column
  2. unpivot other columns
  3. delete column with numeric values
btw. admin column should have header
 

p4nny

Board Regular
Joined
Jan 13, 2015
Messages
246
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

titleAttributeValue
admin2021
1​
admin2022
2​
admin2023
2​
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
try
Code:
// 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
Admin202120222023AdminAttribute
admin122admin2021
admin2022
admin2022
admin2023
admin2023
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,049
You are welcome
Glad you are HAPPY
Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,671
Messages
5,445,888
Members
405,367
Latest member
Tony_Y

This Week's Hot Topics

Top