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"