Forum Discussion

lents2000's avatar
lents2000
Copper Contributor
Apr 03, 2024

Turn one large column into many smaller columns

I have been struggling for days trying to turn my one column into a multiple columns.    Essentially, I was trying to transpose my data to long form to insert into Stata but have been struggling. M...
excel
Excel on Mac
djclements's avatar
djclements
Silver Contributor
Apr 05, 2024

lents2000 I'm curious to know what Power Query steps got you 80% of the way there. You could try using the following code in the Advanced Editor:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Series Name", each if [Attribute] = "Series Name" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Series Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Attribute] <> "Series Name"),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"Series Name"]), "Series Name", "Value")
in
    #"Pivoted Column"

 

Note: change "Table1" to match the name of your source table.

 

Please see the attached sample workbook, if desired...

Resources