英文:
Formatting a list into a table in PowerBI/Excel
问题
I have a list in my Excel file as follows:
如您所见，我只有一个列中的值，其他列为空白，我想要以下结果作为表格：
我有超过99行，有人可以帮助我使用DAX或M代码吗？
谢谢！
英文:
I have a list in my Excel file as folowing :
As you can see i only have the values in one column the others are blanks, i want the folowing result as a table :
i have more than 99 row, can someone help with dax or M code
Thank you !
答案1
得分: 6
以下是翻译好的代码部分：
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
row_groupings = 3,
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> null and [Name] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
Column = Table.TransformColumns(#"Added Index1",{{"Index", each Number.Mod(_, row_groupings), Int64.Type}}),
Row = Table.TransformColumns(Column,{{"Index.1",each Number.IntegerDivide(_, row_groupings), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Row, {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Row, {{"Index", type text}}, "en-US")[Index]), "Index", "Name"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"})
in #"Removed Columns"
请注意，这是翻译后的代码部分，不包括问题的其他内容。
英文:
If it is always three rows, blank, three rows, you can do this in powerquery, pasted into home...advanced editor...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
row_groupings = 3,
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> null and [Name] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
Column = Table.TransformColumns(#"Added Index1",{{"Index", each Number.Mod(_, row_groupings), Int64.Type}}),
Row = Table.TransformColumns(Column,{{"Index.1",each Number.IntegerDivide(_, row_groupings), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Row, {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Row, {{"Index", type text}}, "en-US")[Index]), "Index", "Name"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"})
in #"Removed Columns"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论