在 Power Query 中,您可以分为both根据您的问题的行和列。
- 删除逗号
- 使用分割成行
<line feed>
作为分隔符
- 使用以下命令分割成列
<space>
作为分隔符
-M Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
//Remove the extraneous commas
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Column2"}),
//Split by linefeed into rows
#"Split Column by Delimiter" = Table.ExpandListColumn(
Table.TransformColumns(#"Replaced Value", {
{"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let
itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2", type text}}),
//split on the <space> into columns
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column2",
Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column2.1", "Column2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column2.1", type text}, {"Column2.2", type text}})
in
#"Changed Type2"