我正在 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"
感谢@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"