excel - 并排的梳理表

Question

我的数据设置很糟糕（这是在我之前完成的），我需要跨范围、工作表和文件合并它。

这是一个例子。我正在尝试将所有这三个范围附加到一个表中，并用一列标记该行项目来自哪个类别。

标签： excelpowerbipowerquery

Answer 1

这是处理示例表的一种方法。我从一个名为 Sheet1 的 Excel 工作表开始，如下所示：

然后，在另一个工作簿中，我使用此 M 代码创建了一个 Power Query 查询：

let
    Source = Excel.Workbook(File.Contents("MyPath\MyFile.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(Sheet1_Sheet),
    #"Removed Blank Rows" = Table.SelectRows(#"Transposed Table", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Split Table by 3 Nested" = Table.Split(#"Removed Blank Rows",3),
    #"Inserted Row Nested" = List.Transform(#"Split Table by 3 Nested", each Table.InsertRows(_, Table.RowCount(_),{Record.FromList(List.Repeat({null},Table.ColumnCount(_)),Table.ColumnNames(_))})),
    #"Filled Category Down Nested" = List.Transform(#"Inserted Row Nested", each Table.FillDown(_,{"Column1"})),
    #"Transposed Tables Nested" = List.Transform(#"Filled Category Down Nested", each Table.Transpose(_)),
    #"Removed Blank Rows Nested" = List.Transform(#"Transposed Tables Nested", each Table.SelectRows(_, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))),
    #"Filled Category Down Nested2" = List.Transform(#"Removed Blank Rows Nested", each Table.FillDown(_,{"Column4"})),
    #"Removed First LIne Nested" = List.Transform(#"Filled Category Down Nested2", each Table.Skip(_,1)),
    #"Promoted First Line Nested" = List.Transform(#"Removed First LIne Nested", each Table.PromoteHeaders(_, [PromoteAllScalars=true])),
    #"Renamed Category Column Nested" = List.Transform(#"Promoted First Line Nested", each Table.RenameColumns(_,{{Table.ColumnNames(_){3}, "Category"}})),
    #"Combined Nested Tables" = Table.Combine(#"Renamed Category Column Nested"),
    #"Reordered Columns" = Table.ReorderColumns(#"Combined Nested Tables",{"Category", "Header 1", "Header 2", "Header 3"})
in
    #"Reordered Columns"

要得到这个结果：

大多数变革性工作是通过 List.Transform 在嵌套表中完成的。