Expanding all columns simultaneously in Power Query

Question

Need help expanding all columns in a spreadsheet simultaneously using Power Query. I have transposed the spreadsheet from this:

to this:

Each table is a long column of values (9,000+ rows). I would like each column to be a separate ID. Expanding columns manually would be a tedious job and our team is adding data from new study participants (IDs) regularly, so I need help creating a code that can expand all columns simultaneously without having to indicate the column names (IDs) in the code. Thank you for your help!

This is the code I'm currently using:

let
Source = Folder.Files("folder address goes here"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "filepath", each [Folder Path]&[Name]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Folder Path"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom", each Excel.Workbook(File.Contents([filepath]))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Name"}, {"Name.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Name.1] = "Heart Period Time Series")),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom", each fnImportExcel3([filepath],[Name.1])),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"filepath", "Name.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns2",".xlsx","",Replacer.ReplaceText,{"Name"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")

Hey do you transpose the table, isn‘t it possible to expand the Name Column and the pivot the result? — Chris
We could do that but there are more than 100,000 rows. If we expand the column, the data will be truncated. Once it gets truncated, if you try to pivot or transpose the data, you won't get all of the data (it will still be truncated). — Patricia P

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2020-05-25T21:11:45

You may use following technique:

let
    t1 = #table({"1"},List.Zip({{"a".."f"}})),
    t2 = #table({"2"},List.Zip({{"d".."g"}})),
    t3 = #table({"3"},List.Zip({{"a".."e"}})),
    input = #table({"Name","Custom"},{{"B1",t1},{"B2",t2},{"B3",t3}}),
    toList = Table.TransformColumns(input, {"Custom", Table.ToList}),
    output = #table(toList[Name],List.Zip(toList[Custom]))
in
    output

Expanding all columns simultaneously in Power Query

1 Answers