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. Made some progress with power query to get 80% of the way there.

 

Is there any way to use excel to break my column C for sheet9 into multiple smaller columns? I want the break to happen at every seriess name break in column B (sheet9). I started to do this manually in TableFinal screenshot, but did the math and would take me 30hours to complete due to how much data I have.

 

For context, I am looking at data for 54 countries, with each country having 32 variables tested, each over 9 years.Hopeful outputcurrent

 

excel
Excel on Mac
  • djclements's avatar
    djclements
    Bronze 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...

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 03, 2024

    lents2000 

    =DROP(REDUCE("",UNIQUE(A1:A30),LAMBDA(u,v,VSTACK(u,

    HSTACK(EXPAND(v,5,,v),VSTACK(B1:B5),WRAPCOLS(FILTER(C1:C30,A1:A30=v),5))))),1)

     

    Does this small sample represent what you want to do with a large database? The formula only works with Office 365 and Excel for the web. The assumption is that there are always the 5 cells "Series name", "2001", "2002", "2003" and "2004" for each of the 2 variables (GOP and ABC in this example) of a country.

    • lents2000's avatar
      lents2000
      Copper Contributor
      Apr 04, 2024
      This looks exactly like what I am trying to do, will try and and get back to you

Resources