强大的查询-从单列到整个表的数据转换 [英] 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.

这是我的预期输出，

具有4列的表格

基本上，列值由定界符$$顺序标识，如果column2表示new行，那么它是一条新记录，它必须转到当前行并作为新的列值附加。

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 分组列和汇总到 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}})

现在表格应如下所示：

在这里，您可以使用&& 作为分隔符。

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"

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