Power Query - 从单列到整个表的数据转换 [英] Power Query - Data Transformation from a single column to a whole table

问题描述

我有一个要求，我有这样的表 -

I have a requirement where I have a table like this -

包含 2 列的实际表格

Column1                                              Column2
ColAValue $$ ColBValue $$                            New Row
ColCValue                                            Above Row
ColCValue2                                           Above Row
$$ ColDValue                                         Above Row
ColAValue $$ ColBValue $$ ColCValue $$ ColDValue     New Row
ColAValue $$ ColBValue $$ ColCValue                  New Row
$$ ColDValue                                         Above Row

我知道根据要求，我的数据集中会有 4 列，离开第 2 列.

I know by requirement, I would have 4 columns in my dataset leaving column 2.

我需要使用查询编辑器将转换后的表作为新表.

I need my transformed table as a new table using query editor.

这是我的预期输出，

OutTable 有 4 列

基本上，列值由分隔符 $$ 按顺序标识，如果 column2 表示新行，则它是新记录，否则，它必须将当前行附加为新列值.

Basically the column values are identified in order by delimiter $$ and if column2 says new row, then it is a new record else, it has to go and append on the current row as a new column value.

如何在查询编辑器中将输入表转换为输出表?

How can I transform my Input table to this output table in the query editor?

最终输出的数据类型无关紧要.

The final output Data type doesn't matter.

初始步骤是将上方行中的行值带入带有分隔符的新行并将其作为单行.

The initial step is to bring the row values from Above row into the New row with a delimiter and have it as a single row.

推荐答案

这里的关键是创建一个分组列，将每一行分配给其结果输出行号.您可以通过在 Column2 中使用New Row"查找最后一行的索引来做到这一点.

The key here is to create a grouping column that assigns each row to its resulting output row number. You can do this by looking up the index of the last row with "New Row" in Column2.

首先，创建一个索引列(在添加列"选项卡下).

First, create an index column (under the Add Column tab).

现在您可以通过采用上述最大索引来创建分组自定义列.公式可能如下所示:

Now you can create your grouping custom column by taking the maximal index as described above. The formula might look something like this:

List.Max(
    Table.SelectRows(#"Prev Step Name",
        (here) => [Index] >= here[Index] and here[Column2] = "New Row"
    )[Index]
)

您的表格现在应该如下所示:

Your table should look like this now:

现在我们使用 Group By(在 Home 标签下)，按 Group 列分组并在 Column1 上进行聚合.

Now we use Group By (under Home tab), grouping by the Group column and aggregating over Column1.

但是我们要将聚合从 List.Max 更改为 Text.Combine 以便这一步的代码是

But we're going to change the aggregation from List.Max to Text.Combine so that the code for this step is

= Table.Group(#"Added Custom", {"Group"},
      {{"Concat", each Text.Combine([Column1]," "), type text}})

现在表格应该是这样的:

Now the table should look like this:

从这里，您可以使用 " && " 作为分隔符来按分隔符拆分列(在主页选项卡下).

From here, you can do Split Column By Delimiter (under Home tab) using " && " as your delimiter.

根据需要更改任何列名称，如果您不再需要它，则删除 Group 列，结果应该是您需要的输出.

Change any column names as desired and delete the Group column if you no longer want it and the result should be your required output.

整个查询的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"

这篇关于Power Query - 从单列到整个表的数据转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！