☰

使用 PowerQuery 重命名或扩展可变数量的列

tag-icon • tag-icon microsoft-excel power-query
使用 PowerQuery 重命名或扩展可变数量的列

我有如下所示的数据表：

Report Date |   ID      |   Percent
11/06/2021  |   12345   |   0.9
18/06/2021  |   12345   |   1
25/06/2021  |   12345   |   0.85
02/07/2021  |   12345   |   0.5
11/06/2021  |   54321   |   0.77
18/06/2021  |   54321   |   1
25/06/2021  |   54321   |   1
02/07/2021  |   54321   |   0.25

每个日期都有 ID 和一个百分比值。
有时表格中有四周，有时有五周，具体取决于工资期（一个月可能有四周或五周）。

我有一个查询，它按 ID 对数据进行分组，并返回四周内的平均百分比数字。

现在，我希望无需详细说明，就可以将这些日期更改为第 1 周、第 2 周、第 3 周、第 4 周，偶尔还会更改为第 5 周（我的老板希望在平均值旁边看到各个星期）。
如果我将表格旋转，使每个星期都变成一列，那么我需要知道列的名称，然后才能展开它：

let
    Source = Table.NestedJoin(#"My Query", {"ID"}, #"Pivotted Table", {"ID"}, "Pivotted Table", JoinKind.LeftOuter),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"ID", "Pivotted Table", "Percent"}),
    #"Expanded Pivotted Table" = Table.ExpandTableColumn(#"Reordered Columns", "Pivotted Table", {"11/06/2021", "18/06/2021", "25/06/2021", "02/07/2021"}, {"11/06/2021", "18/06/2021", "25/06/2021", "02/07/2021"})
in
    #"Expanded Pivotted Table"

我发现了一个功能它允许我根据列在表中的位置重命名列，这样我就可以将列名标准化为第 1 周、第 2 周、第 3 周等。但由于偶尔会出现第 5 周，因此仍然会引发错误。

//Function Name - Rename Columns
let
    RenameColumns = (InputTable as table, ColumnNumbers as list, NewColumnNames as list) =>
let
    OldColumnNames = Table.ColumnNames(InputTable),
    Indexed = List.Zip({OldColumnNames, {0..-1+List.Count(OldColumnNames)}}),
    Filtered = List.Select(Indexed, each List.Contains(ColumnNumbers,_{1})),
    IndexRemoved = List.Transform(Filtered, each _{0}),
    RenameList = List.Zip({IndexRemoved,NewColumnNames}),
    RenamedColumns = Table.RenameColumns(InputTable, RenameList)
in
    RenamedColumns
in
    RenameColumns

因此，如果没有第六列（第一列是第 0 列），则会引发错误。

//Query Name - Pivotted Table
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"ID", type text}, {"Percent", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Report Date", type text}}, "en-GB")[#"Report Date"]), "Report Date", "Percent"),
    RenameColumns = #"Rename Columns"(#"Pivoted Column",{1,2,3,4,5}, {"Week 1", "Week 2", "Week 3", "Week 4", "Week 5"})
in
     RenameColumns

问题：

如何重命名或扩展可变数量的列？

答案1

从原始日期（而不是数据透视表）开始，以下内容应该允许可变数量的列。

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Report Date", type date}, {"ID", Int64.Type}, {"Percent", type number}}),

//Create the arrays for the week date => weekname transformation
//Assuming that weeks are numbered according to the position in a date-sorted list, and not by the week number of the month or year
//Column Names must be strings, so we have to convert the dates to the equivalent strings
oldNames = List.Transform(List.Sort(List.Distinct(#"Changed Type"[#"Report Date"])), each Text.From(_)),

//use List.Generate to create the new column names
newNames = List.Generate(()=>[colName="Week 1", IDX=2],
                each [IDX] < List.Count(oldNames)+2,
                each [colName="Week" & Text.From([IDX]), IDX = [IDX]+1],
                each [colName]),

//Then group and Pivot however you have done this
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Report Date", "ID"}, {{"Average", each List.Average([Percent]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", 
        {{"Report Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Report Date", type text}}, "en-US")[#"Report Date"]), "Report Date", "Average", List.Average),

//Rename the columns here
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",List.Zip({oldNames,newNames})),

//Re-Order the columns to ensure they are in the proper sorted order
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",newNames)
in
    #"Reordered Columns"

相关内容