主页 / computer / 问题 / 1810276
Accepted
Dave
Asked: 2023-09-27 23:35:34 +0800 CST

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

  • 772

我在 Excel for Microsoft 365 中使用 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 才能获得所需的结果？

microsoft-excel

1 个回答

  1. Best Answer

    下面的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"
    • 0

相关问题