展开power查询中的所有列(可变列数)
展开power查询中的所有列(可变列数)
提问于 2021-11-07 11:14:08
我在power查询中有一个数据模型，它生成某种类型的组合列表。
在某种程度上，我创建了模型，但我有一个步骤，我不知道该如何做。
我需要将所有命名的列从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])在#“提升标题”中
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-11-07 11:53:37
您可以使用递归函数从输入中获取输出。
我给这个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
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69871922
复制相关文章
相似问题
腾讯云开发者
