Rename or expand a variable number of columns with PowerQuery
I have a data table that looks like below:
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
Each ID appears for each date along with a Percent value.
Sometimes there are four weeks in the table, sometimes five depending on the pay period (a month could have four or five weeks).
I have a query that groups the data by ID and returns an average percent figure across the four weeks.
Now, hopefully without having to go into full details, I want to change these dates into Week 1, Week 2, Week 3, Week 4 and occasionally Week 5 (my boss wants to see the individual weeks next to the average value).
If I pivot the table so that each week becomes a column then I need to know the name of the column before I can expand it:
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"
I found a function that allows me to rename columns based on their position in the table so I could standardise the column names to Week 1, Week 2, Week 3, etc. This still throws an error as there's an occasional Week 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
So this throws an error if there's no sixth column (first column is column 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
Question:
How do I rename or expand a variable number of columns?
Starting with your raw date (not the pivoted table), the following should allow for a variable number of columns.
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"