在Power Query中同时扩展所有列 [英] Expanding all columns simultaneously in Power Query

问题描述

需要使用Power Query帮助同时扩展电子表格中的所有列.我已经将电子表格从此换位:

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

对此:

每个表都是一长列值(9,000多行).我希望每一列都是一个单独的ID.手动扩展列是一项繁琐的工作，我们的团队定期添加来自新研究参与者(ID)的数据，因此我需要帮助来创建一个可以同时扩展所有列而无需在代码中指出列名(ID)的代码.谢谢您的帮助！

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")

推荐答案

您可以使用以下技术:

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

这篇关于在Power Query中同时扩展所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！