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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
use Power Query
  1. select admin column
  2. unpivot other columns
  3. delete column with numeric values
btw. admin column should have header
 
Upvote 0
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​
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top