这里的关键是创建一个分组列,将每一行分配给其结果输出行号。您可以通过在中查找带有“New Row”的最后一行的索引来完成此操作Column2
.
首先,创建一个索引列(在“添加列”选项卡下)。
现在,您可以通过采用如上所述的最大索引来创建分组自定义列。该公式可能如下所示:
List.Max(
Table.SelectRows(#"Prev Step Name",
(here) => [Index] >= here[Index] and here[Column2] = "New Row"
)[Index]
)
您的表格现在应该如下所示:
现在我们使用 Group By(在“Home”选项卡下),按Group
列并聚合Column1
.
但我们要将聚合从List.Max
to Text.Combine
所以这一步的代码是
= Table.Group(#"Added Custom", {"Group"},
{{"Concat", each Text.Combine([Column1]," "), type text}})
现在该表应如下所示:
从这里,您可以使用“按分隔符拆分列”(在“主页”选项卡下)" && "
作为你的分隔符。
根据需要更改任何列名称并删除Group
列(如果您不再需要它)并且结果应该是您所需的输出。
整个查询的M代码:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PcQxLzClNVVBRUQBynGAcJR0lv9RyhaD8cqVYHbA6Z7AUUNwxKb8sFVPGCEMKYqQLTn3YbVaAm6iAZgCagwhpR9OB2zWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column1", "Column2"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Group", each List.Max(Table.SelectRows(#"Reordered Columns", (here) => [Index] >= here[Index] and here[Column2] = "New Row")[Index]), Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"Concat", each Text.Combine([Column1]," "), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Concat", Splitter.SplitTextByDelimiter(" $$ ", QuoteStyle.Csv), {"COL1", "COL2", "COL3", "COL4"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Group"})
in
#"Removed Columns"