☰

Power Query：将多值单元格拆分为行而不创建所有可能的组合

tag-icon • tag-icon microsoft-excel power-query
Power Query：将多值单元格拆分为行而不创建所有可能的组合

我正在 Microsoft 365 的 Excel 中使用 Power Query。

我的一些列有多值单元格。我需要将这些单元格拆分成行。考虑以下源数据：

源数据

我的 Power Query 的 M 语言代码如下；

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pet Type", type text}, {"Items Needed", type text}, {"Item Purpose", type text}, {"Typical Name", type text}}),
    #"Split Items Needed" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Items Needed", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Items Needed"),
    #"Split Item Purpose" = Table.ExpandListColumn(Table.TransformColumns(#"Split Items Needed", {{"Item Purpose", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item Purpose")
in
    #"Split Item Purpose"

实际结果如下：

实际结果

期望结果如下：

期望结果

我必须如何调整 Power Query 才能获得所需的结果？

答案1

以下M语言代码将解决这个问题：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type of All to Text" = Table.TransformColumnTypes(Source,{{"Pet Type", type text}, {"Items Needed", type text}, {"Item Purpose", type text}, {"Typical Name", type text}}),
    #"Split Items Needed" = Table.AddColumn(#"Changed Type of All to Text", "Split Items Needed", each Text.Split([Items Needed],"#(lf)")),
    #"Split Item Purpose" = Table.AddColumn(#"Split Items Needed", "Split Item Purpose", each Text.Split([Item Purpose],"#(lf)")),
    #"Match Splits Step 1" = Table.AddColumn(#"Split Item Purpose", "Split Items", each Table.FromColumns({[Split Items Needed],[Split Item Purpose]})),
    #"Match Splits Step 2" = Table.ExpandTableColumn(#"Match Splits Step 1", "Split Items", {"Column1", "Column2"}, {"Items Needed.1", "Item Purpose.1"}),
    #"Changed New Columns to Text" = Table.TransformColumnTypes(#"Match Splits Step 2",{{"Items Needed.1", type text}, {"Item Purpose.1", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed New Columns to Text",{"Pet Type", "Typical Name", "Items Needed.1", "Item Purpose.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Items Needed.1", "Items Needed"}, {"Item Purpose.1", "Item Purpose"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Pet Type", "Items Needed", "Item Purpose", "Typical Name"})
in #"Reordered Columns"

来源：
https://stackoverflow.com/questions/62798233/splitting-multiple-multi-valued-cells-in-excel-into-rows

感谢@horseyride在上述链接中提供的答案。

编辑以添加第二个解决方案

以下解决方案（也归功于上面链接的问题）非常优雅，因为它适用于任意数量的列：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    #"TableTransform" = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"), (x) => List.Transform(Record.ToList(x), each Text.Split(_, "#(lf)"))), each Table.FromColumns(_, Table.ColumnNames(#"Changed Type")))),
    #"Changed Type1" = Table.TransformColumnTypes(#"TableTransform", List.Transform(Table.ColumnNames(#"TableTransform"), each {_, type text})),
    #"Filled Down" = Table.FillDown(#"Changed Type1", Table.ColumnNames(#"Changed Type1"))
in #"Filled Down"

相关内容