powerbi - 展开幂查询中的所有列（列数可变）

Question

我在电源查询中有一个数据模型，可以生成某种组合列表。

我在某种程度上创建了模型，但我有一个我不知道该怎么做的步骤。

我需要将所有从 1 命名的列扩展到总行数（列数是可变的）。行来自源表

扩展到我需要的新行，是的，这将是一个非常长的列表，这正是我想要的

结果表需要包含3528 行。

重要提示：逐步扩展是不可接受的，因为列数会有所不同。

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYpLCoAwDETvknVhrKB7//9VkMaWXsPzG4oWXAyZlzch0IYdDiIQz3oEzpKhWkPRBGrQo8MMD2bWZ6W5k2mxYFIqv+mBVavNg0Fbken80fVSgvGnhH2W8QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom.1 = _t, Custom = _t, variatie = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Custom.1", type text}, {"Custom", Int64.Type}}),
#"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"variatie", Text.Lower, type text}}),
#"Added Custom" = Table.AddColumn(#"Lowercased Text", "Custom.2", each if [variatie] = "v" then [Custom.1] & "/" else [Custom.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Custom.1", "variatie"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.2", "Custom.1"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Custom.1", "Custom.1 - Copy"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Custom.1 - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom.1 - Copy"),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Custom", "Custom.1"}, {{"Count", each _, type table [Custom=nullable number, Custom.1=nullable text, #"Custom.1 - Copy"=nullable text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.2", each [Count][#"Custom.1 - Copy"]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Custom.1", "Count"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Custom.2", "Custom.1"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Custom"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Custom.1"}),
#"Transposed Table" = Table.Transpose(#"Reordered Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])

在#“升级的标题”中

标签： powerbipowerquery

Answer 1

您可以使用递归函数从输入中获取输出。

我把这个命名为fnExpandAll

将此作为空白查询输入并重命名：

(t as table, optional colNum as number) =>

let 
    colNames = Table.ColumnNames(t),
    Index = if colNum = null then  0 else colNum,
    expand = Table.ExpandListColumn(t, colNames{Index}),
    nextCol = Index+1,
    repeat = if nextCol < List.Count(colNames) then 
        fnExpandAll(expand, nextCol) else expand
in 
    repeat

然后，您将这些行添加到您发布的代码的底部：

//...
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),

//add these lines

    expandAll = fnExpandAll(#"Promoted Headers")
in
    expandAll

编辑： 如果您想扩展每个列表，但不生成示例结果中显示的额外行，则可以将自定义函数简化为：

(t as table) =>

let 
    colNames = Table.ColumnNames(t),
    columns = List.Transform(Table.ToColumns(t), each List.Combine(_)),
    expand = Table.FromColumns(columns, colNames)
    
in 
    expand