如果表格中的9个单元格被配置为名为“Data”的Excel表格,则M代码如下:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Section A", type text}, {"Section B", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Section A", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Section A"),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Section B", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Section B"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"ID"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Section"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Value", Text.Trim, type text}}),
#"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
#"Removed Duplicates"
将在PowerQuery中呈现此结果
(您需要
按分隔符拆分列
(每个部分的步骤)