Power Query Workout 05 - Allocate Weeks to Months

[Matthias]

Difficulty Rating:

Introduction:

You get the data for tracking and planning of the production volumes per week.
And of course you have to report them by month.
Your company is not using ISO weeks, but weeks like the Date.WeekOfYear function provides in Power Query.
Weeks start on Mondays!
Production runs only from Monday-Friday, so when you have to allocate the weeks to the months you need to decide it based on in which month Wednesday is in.
The logic behind this criteria is that by this the majority of the production days of that week are in that month.

Your task:
Aggregate the weekly figures to monthly figures.
Build a dynamic solution which is independent from the number of weeks or products.

(NOTE: For more advanced users, attempt to minimize the number of manual changes using relevant M code functions)

5 Weeks to Months.xlsx (22.4 KB)

This workout was posted on Monday April 24, 2023, and the author’s solution will be posted on Sunday April 30, 2023.

[Luan]

let
    Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content],
    din = Table.UnpivotOtherColumns(Source, {}, "Atributo", "Valor"),
    sub = Table.ReplaceValue(din,"wk0","wk",Replacer.ReplaceText,{"Atributo"}),
    tab = 
    let
        Fonte = {Number.From(StartDate)..Number.From(EndDate)},
        conv = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        tipo = Table.TransformColumnTypes(conv,{{"Column1", type date}}),
        data = Table.AddColumn(tipo, "dados", each [
        sm = Date.WeekOfMonth([Column1],1),
        sa = Text.Combine({"wk",Text.From(Date.WeekOfYear([Column1],1))}),
        ds = Date.DayOfWeek([Column1]),
        mes_ano = Date.MonthName([Column1],"en-us")]),
        exp = Table.ExpandRecordColumn(data, "dados", Record.FieldNames(data[dados]{0}) ),
        gp = Table.Group(exp, {"mes_ano"}, {{"Contagem", each 
        [
        a = _,
        b = Table.SelectRows(Table.Group(a, {"sa"},{{"Count", each List.Count(_[sa])}}), each [Count]>=4)
        ][b]
        }}),
        con_e = Table.ExpandTableColumn(gp, "Contagem", {"sa"})
    in
        con_e,
    mesc = Table.NestedJoin(sub, {"Atributo"}, tab, {"sa"}, "Consulta1", JoinKind.LeftOuter),
    exp = Table.ExpandTableColumn(mesc, "Consulta1", {"mes_ano"}),
    add = Table.AddColumn(exp, "Personalizar", each if [Atributo] = "Product" then [Valor]  else null),
    pb = Table.FillDown(add,{"Personalizar","mes_ano"}),
    fil = Table.SelectRows(pb, each ([mes_ano] <> null and [Atributo] <> "Product")),
    col = Table.Group(fil, {"mes_ano", "Personalizar"}, {{"Contagem", each List.Sum([Valor]), type any}}),
    res = Table.Pivot(col, List.Distinct(col[mes_ano]), "mes_ano", "Contagem")
in
    res

[borydobon]

let
    Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content],
    AWed = Table.AddIndexColumn(Table.PromoteHeaders(Table.Transpose(Source)),"Product", let st =StartDate in Number.From(Date.AddDays(st,-Date.DayOfWeek(st,3))),7),
    Month = Table.TransformColumns(Table.SelectRows(Table.TransformColumns(AWed,{"Product",each Date.From(_)}),each Date.Year([Product])=Date.Year(StartDate)),{"Product",each  Date.MonthName(_,"en")}),
    Group = Table.ExpandRecordColumn(Table.Group(Month, "Product", {"T", each Record.FromList(List.Transform(List.RemoveLastN(Table.ToColumns(_),1),List.Sum),Source[Product])}),"T",Source[Product]),
    Transpose = Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Group)))
in
    Transpose

[AlexisOlson]

let
    Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Product"}, "Week", "Value"),
    TextToInt = Table.TransformColumns(Unpivot, {{"Week", each Number.From(Text.AfterDelimiter(_, "wk")), Int64.Type}}),
    AddWednesday = Table.AddColumn(TextToInt, "Wednesday", each Date.AddDays(StartDate, 7 * ([Week] - 1) - Date.DayOfWeek(StartDate, Day.Wednesday)), type date),
    FilterDates = Table.SelectRows(AddWednesday, each [Wednesday] >= StartDate and [Wednesday] <= EndDate),
    AddMonth = Table.AddColumn(FilterDates, "Month", each Date.ToText([Wednesday], [Format = "MMMM", Culture="en-US"]), type text),
    GroupBy = Table.Group(AddMonth, {"Product", "Month"}, {{"Value", each List.Sum([Value]), type number}}),
    PivotMonth = Table.Pivot(GroupBy, List.Distinct(GroupBy[Month]), "Month", "Value")
in
    PivotMonth

[BrianJ]

Definitely not the most efficient code I’ve ever written, but it works and is dynamic…

let
    Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content],
    UnpivotOther = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value"),
    SplitByWk = Table.SplitColumn(UnpivotOther, "Attribute", Splitter.SplitTextByDelimiter("wk", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    ReType = Table.RenameColumns( Table.RemoveColumns( Table.TransformColumnTypes(SplitByWk,{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), "Attribute.1"), {"Attribute.2", "WeekNum"}),
   WeekMonthTable = [  
        DateList = List.Transform( {Number.From( StartDate)..Number.From(EndDate)}, each Date.From( _ )),
        ToTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}, {Date.Type}, ExtraValues.Error),
        AddWeekDates = Table.FillDown( Table.AddColumn(ToTable, "Week", each 
            if [Date] = StartDate then [Date] else 
           if Date.DayOfWeekName( [Date] ) = "Monday" then [Date] else null), {"Week"}),
        Group = Table.Group(AddWeekDates, {"Week"}, {{"All", each _, type table [Date=date, Week=date]}}),
        AddIndex = Table.AddIndexColumn(Group, "Index", 1, 1, Int64.Type),
        ExpandAll = Table.ExpandTableColumn(AddIndex, "All", {"Date"}, {"Date"}),
        AddMonthName = Table.AddColumn(ExpandAll, "MonthName", each Date.MonthName( [Date] )),
        AddDayName = Table.AddColumn(AddMonthName, "DayName", each Date.DayOfWeekName( [Date] )),
        AddKeepCond = Table.AddColumn(AddDayName, "Keep", each 
            if [Date] = StartDate then 1 else
            if [DayName] = "Wednesday" then 1 else 
            0),
        Filter = Table.SelectColumns( Table.SelectRows(AddKeepCond, each ([Keep] = 1)), {"Index", "MonthName"})
   ][Filter],
    Join = Table.RemoveColumns( Table.Join( ReType, "WeekNum", WeekMonthTable, "Index"), {"WeekNum", "Index"}),
    Pivot = Table.Pivot(Join, List.Distinct(Join[MonthName]), "MonthName", "Value", List.Sum)
in
    Pivot

[willow.lang]

let
Source =
Excel.CurrentWorkbook(){[Name=“Production”]}[Content],

UnpivotedOtherColumns = 
    Table.UnpivotOtherColumns(Source, {"Product"}, "Week", "Volume"),

TransformWeek = 
    Table.TransformColumns(UnpivotedOtherColumns, {{"Week", each Number.FromText(Text.End(_,2)),Int64.Type}}),

Calendar = 
let
    TableofDates =  
        Table.FromList(
            List.Dates(StartDate,Duration.Days(EndDate-StartDate)+1,#duration(1,0,0,0)), 
            Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    WeekofYear = 
        Table.AddColumn(TableofDates, "Week of Year", each Date.WeekOfYear([Column1],Day.Monday), Int64.Type),
    DayofWeek = 
        Table.AddColumn(WeekofYear, "Day of Week", each Date.DayOfWeek([Column1],Day.Monday), Int64.Type),
    SelectWednesday = 
        Table.SelectRows(DayofWeek, each ([Day of Week] = 2)),
    MonthName = 
        Table.RemoveColumns(Table.AddColumn(SelectWednesday, "Month Name", each Date.MonthName([Column1],"en-GB"), type text),{"Column1","Day of Week"})
in
    MonthName,

Merge_calendar = 
    Table.NestedJoin(TransformWeek,{"Week"},Calendar,{"Week of Year"},"Month",JoinKind.LeftOuter),

Expand_Month = 
    Table.TransformColumnTypes(    
        Table.RemoveColumns(
            Table.ReplaceValue(
                Table.ExpandTableColumn(Merge_calendar, "Month", {"Month Name"}, {"Month Name"}),
                each [Month Name],
                each if [Week]=1 and [Month Name] = null then "January" else [Month Name],
                Replacer.ReplaceValue,
                {"Month Name"}
            ),
            {"Week"}
        ),
        {{"Product", type text}, {"Month Name", type text}, {"Volume", Int64.Type}}),

PivotColumn = 
    Table.Pivot(Expand_Month, List.Distinct(Expand_Month[#"Month Name"]), "Month Name", "Volume", List.Sum)

in
PivotColumn

[valeriabreveglieri]

Hello!
For this one I went for a calendar table to help with the calculations, as I thought that it might be needed anyway for the report. I used the calendar table by RADACAD which is excellent, shaped to what is needed (start of the week = Monday, added the columns for finding the Wednesday of each week and corresponding month). I assumed the calculations were for the current year, but with the calendar table it is easy to extend to several years if the year columns is added to the initial data and then the lookup is done both on year and week.

Thanks for the nice challenge as always!

So here it goes:

CalendarTable

let
// configurations start
Today=Date.From(DateTime.LocalNow()), // today’s date
FromYear = Date.Year(DateTime.FixedLocalNow()), // set the start year of the date dimension. dates start from 1st of January of this year
ToYear=Date.Year(DateTime.FixedLocalNow()), // set the end year of the date dimension. dates end at 31st of December of this year
StartofFiscalYear=1, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
firstDayofWeek=Day.Monday, // set the week’s start day, values: Day.Monday, Day.Sunday…
// configuration end
FromDate=#date(FromYear,1,1),
ToDate=#date(ToYear,12,31),
Source=List.Dates(
FromDate,
Duration.Days(ToDate-FromDate)+1,
#duration(1,0,0,0)
),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Renamed Columns” = Table.RenameColumns(#“Converted to Table”,{{“Column1”, “Date”}}),
#“Changed Type” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Date”, type date}}),
#“Inserted Year” = Table.AddColumn(#“Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
#“Inserted Start of Year” = Table.AddColumn(#“Inserted Year”, “Start of Year”, each Date.StartOfYear([Date]), type date),
#“Inserted End of Year” = Table.AddColumn(#“Inserted Start of Year”, “End of Year”, each Date.EndOfYear([Date]), type date),
#“Inserted Month” = Table.AddColumn(#“Inserted End of Year”, “Month”, each Date.Month([Date]), Int64.Type),
#“Inserted Start of Month” = Table.AddColumn(#“Inserted Month”, “Start of Month”, each Date.StartOfMonth([Date]), type date),
#“Inserted End of Month” = Table.AddColumn(#“Inserted Start of Month”, “End of Month”, each Date.EndOfMonth([Date]), type date),
#“Inserted Days in Month” = Table.AddColumn(#“Inserted End of Month”, “Days in Month”, each Date.DaysInMonth([Date]), Int64.Type),
#“Inserted Day” = Table.AddColumn(#“Inserted Days in Month”, “Day”, each Date.Day([Date]), Int64.Type),
#“Inserted Day Name” = Table.AddColumn(#“Inserted Day”, “Day Name”, each Date.DayOfWeekName([Date]), type text),
#“Inserted Day of Week” = Table.AddColumn(#“Inserted Day Name”, “Day of Week”, each Date.DayOfWeek([Date],firstDayofWeek), Int64.Type),
#“Inserted Day of Year” = Table.AddColumn(#“Inserted Day of Week”, “Day of Year”, each Date.DayOfYear([Date]), Int64.Type),
#“Inserted Month Name” = Table.AddColumn(#“Inserted Day of Year”, “Month Name”, each Date.MonthName([Date]), type text),
#“Inserted Quarter” = Table.AddColumn(#“Inserted Month Name”, “Quarter”, each Date.QuarterOfYear([Date]), Int64.Type),
#“Inserted Start of Quarter” = Table.AddColumn(#“Inserted Quarter”, “Start of Quarter”, each Date.StartOfQuarter([Date]), type date),
#“Inserted End of Quarter” = Table.AddColumn(#“Inserted Start of Quarter”, “End of Quarter”, each Date.EndOfQuarter([Date]), type date),
#“Inserted Week of Year” = Table.AddColumn(#“Inserted End of Quarter”, “Week of Year”, each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
#“Inserted Week of Month” = Table.AddColumn(#“Inserted Week of Year”, “Week of Month”, each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
#“Inserted Start of Week” = Table.AddColumn(#“Inserted Week of Month”, “Start of Week”, each Date.StartOfWeek([Date],firstDayofWeek), type date),
#“Inserted End of Week” = Table.AddColumn(#“Inserted Start of Week”, “End of Week”, each Date.EndOfWeek([Date],firstDayofWeek), type date),
#“Added Wednesday” = Table.AddColumn(#“Inserted End of Week”, “Wednesday of Week”, each Date.AddDays([Start of Week],2), type date),
#“Add Month of Wednesday” = Table.AddColumn(#“Added Wednesday”, “Month of the Wednesday”, each Date.MonthName([Wednesday of Week]), Int64.Type),
#“Filtered Rows” = Table.SelectRows(#“Add Month of Wednesday”, each [Wednesday of Week] > #date(FromYear, 1, 1))
in
#“Filtered Rows”

let
Source = Excel.Workbook(File.Contents(“myPath\5 Weeks to Months.xlsx”), null, true),
Production_Table = Source{[Item=“Production”,Kind=“Table”]}[Data],
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(Production_Table, {“Product”}, “Attribute”, “Value”),
#“Changed Type” = Table.TransformColumnTypes(#“Unpivoted Other Columns”,{{“Value”, Int64.Type}, {“Product”, type text}}),
#“Extracted Text After Delimiter” = Table.TransformColumns(#“Changed Type”, {{“Attribute”, each Text.AfterDelimiter(_, “wk”), type text}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Extracted Text After Delimiter”,{{“Attribute”, Int64.Type}}),
#“Added Custom” = Table.AddColumn(#“Changed Type1”, “Month”, each try Table.SelectRows(CalendarTable, (x)=> x[Week of Year] = [Attribute])[Month of the Wednesday]{0} otherwise null),
#“Filtered Rows” = Table.SelectRows(#“Added Custom”, each [Month]<>null),
#“Removed Columns” = Table.RemoveColumns(#“Filtered Rows”,{“Attribute”}),
#“Pivoted Column” = Table.Pivot(Table.TransformColumnTypes(#“Removed Columns”, {{“Month”, type text}}, “en-US”), List.Distinct(Table.TransformColumnTypes(#“Removed Columns”, {{“Month”, type text}}, “en-US”)[Month]), “Month”, “Value”, List.Sum)
in
#“Pivoted Column”

[Romain_974]

Hi,
I’ve tried something like that.
Seems to work

let
    Source = 
        Table.Distinct(
            Table.FromRecords(
                List.Generate( 
                    () => [ x = StartDate , 
                            y ="wk" & Number.ToText( Date.WeekOfYear(StartDate, Day.Monday), "00") , 
                            z = Date.MonthName(Date.EndOfWeek(StartDate, Day.Thursday), "MMMM")
                        ], 
                    each [x] < EndDate , 
                    each [  x = Date.AddDays([x],1), 
                            y =  "wk" & Number.ToText( Date.WeekOfYear(Date.AddDays([x],1) , Day.Monday), "00"),  
                            z = Date.MonthName(Date.EndOfWeek(Date.AddDays([x],1), Day.Thursday), "MMMM")
                        ], 
                    each [[y] , [z]] 
                )
            ), 
            "y"
        ),
    AddCol = 
        List.Accumulate(
            Production[Product], 
            Source, 
            (output, input) => 
                Table.AddColumn(
                    output, 
                    input, 
                    each Record.Field( Production{[Product = input]}, [y] ) 
                )
        ),
    Result = 
        List.Accumulate( 
            List.Distinct( AddCol[z]), 
            Table.FromList( Production[Product], Splitter.SplitByNothing()), 
            (output, input) => 
                Table.AddColumn (
                    output, 
                    input, 
                    each let 
                        T1 = Table.SelectRows(AddCol, each [z] = input),
                        T2 =  List.Sum( Table.Column(T1, [Column1])) 
                    in T2 
                )
        )
in
    Result

[Matthias]

Hello,
thanks to everyone who participated! There were again very different approaches, some of them really cool and compact, so it is definitely worth having a look at them.

As always I’ll present a solution which is as much as possible UI orientated, so that you can follow along even if you are not an advanced user. It consists of two separate portions, which makes it easier to understand.

Let’s start off with building a WeekMonth table allocating a month name to each week.
There is a good chance that you have already a calendar which you can reference to:

For our purpose we only need the Date column, so you can type Date in the formula bar in two square brackets:
= Calendar[[Date]]

If you haven’t got a calendar table, you can create a new query:

Then add in the formula bar a list from StartDate to EndDate in curly brackets:
= {Number.From(StartDate)…Number.From(EndDate)}
Both have to be wrapped in Number.From!

To transform that list to a table click on the List Tools tab on To Table and then press OK:

Change the date type to date (rightmouse > Change Type > Date).
Now we have almost achieved the same status like above with referencing of an existing calendar table. The only difference is that our column is called Column1 instead of Date. Rename if you want or just leave it as it is.

Mark Date/Column1 and select on the Add Column tab Date > Week > Week of Year

Once more on the Add Column tab select Date > Week > End of Week

As we want to have our weeks “end” on Wednesdays we add a 4 in the formula bar:
= Table.AddColumn(#“Inserted Week of Year”, “End of Week”, each Date.EndOfWeek([Column1], 4), type date)

Now mark the End of Week column and go for Date > Month > Name of Month

Lastly mark the Week of Year column and on the Home tab Remove Rows > Remove Duplicates

let
    Source = {Number.From(StartDate)..Number.From(EndDate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Column1]), Int64.Type),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Week of Year", "End of Week", each Date.EndOfWeek([Column1], 4), type date),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted End of Week", "Month Name", each Date.MonthName([End of Week], "en-US"), type text),
    #"Removed Duplicates" = Table.Distinct(#"Inserted Month Name", {"Week of Year"})
in
    #"Removed Duplicates"

That gives us a table which allocates each week of the year to the correct month.
Quite some steps, but only as the code starts from scratch with the calendar building. Less steps with referencing to a calendar table and then there are only small adjustments required in the formula bar.

Now we can use this in the Production query. Again we are clicking around, so you can definitely shorten the code if you want to.

Mark the Product column (respectively all non wk columns) and on the Transform tab select Unpivot Columns > Unpivot Other Columns:

Mark the Attribute column and still on the Transform tab select Extract > Last Characters and input 2

Mark all columns and click on the Transform tab Detect Data Type

Now we click on the Home tab Merge Query and merge the query with the WeekMonth query from above with the week number as key.

Let’s expand the newly added WeekMonth column and we only need the Month Name:

Mark the Attribute column and press Delete to get rid of it.

And then finally we mark the Month Name column and on the Transform tab we select Pivot Column.
Choose Value as Values Column and you’re done!

let
    Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.End(_, 2), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Last Characters",{{"Product", type text}, {"Attribute", Int64.Type}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Attribute"}, WeekMonth, {"Week of Year"}, "WeekMonth", JoinKind.LeftOuter),
    #"Expanded WeekMonth" = Table.ExpandTableColumn(#"Merged Queries", "WeekMonth", {"Month Name"}, {"Month Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded WeekMonth",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month Name]), "Month Name", "Value", List.Sum)
in
    #"Pivoted Column"

That’s 100% UI supported!

Try it out and make sure to check also some of the other approaches above! => Hope you learn something!

Big thanks to everyone who has blurred or hidden the details! I have taken this now out so that it’s easier to compare solutions.

[AlejandroSimon]

Adjunto mi query…

let
Source = Excel.CurrentWorkbook(){[Name=“Production”]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {“Product”}, “Attribute”, “Value”),
Extract = Table.TransformColumns(Unpivot, {{“Attribute”, each Number.From(Text.End(, 2))}}),
Fecha = Table.SelectRows(Table.Skip(Table.AddColumn(Extract, “Fecha”, each Date.StartOfWeek(Date.AddWeeks(StartDate, [Attribute]-1), Day.Wednesday))), each [Fecha] <= EndDate),
Month = Table.RemoveColumns(Table.TransformColumns(Fecha, {{“Fecha”, each Date.MonthName(), type text}}), “Attribute”),
Sol = Table.Pivot(Month, List.Distinct(Month[Fecha]), “Fecha”, “Value”, List.Sum)
in
Sol

[SamMcKay]

I was on my iPad and wanted to do some workouts.

This is a tricky one because I can’t use power query to test my results so I got innovative.

I used chatGPT, but then tested the result based on other code that was in the thread!

let
    Source = Excel.CurrentWorkbook(){[Name="Production"]}[Content],
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    UnpivotColumns = Table.UnpivotOtherColumns(PromoteHeaders, {"Product"}, "Attribute", "Value"),
    ExtractWeekNumber = Table.TransformColumns(UnpivotColumns, {{"Attribute", each Text.End(_, 2), Int64.Type}}),
    AddMonthNumber = Table.AddColumn(ExtractWeekNumber, "Month", each Number.RoundUp([Attribute] / 4), Int64.Type),
    ExtractValues = Table.TransformColumns(AddMonthNumber, {{"Value", each Text.Remove(_, ","), type number}}),
    PivotData = Table.Pivot(ExtractValues, {"Product", "Month"}, "Attribute", "Value", List.Sum),
    FillEmptyCells = Table.FillDown(PivotData, {"Product"}),
    ReplaceNulls = Table.ReplaceValue(FillEmptyCells, null, 0, Replacer.ReplaceValue),
    SortColumns = Table.Sort(ReplaceNulls, List.Transform(Table.ColumnNames(ReplaceNulls), each {_, Order.Ascending})),
    ReorderColumns = Table.ReorderColumns(SortColumns, {"Product"} & List.RemoveFirstN(Table.ColumnNames(SortColumns), 1)),
    Result = ReorderColumns
in
    Result

Because I haven’t tested this for real yet, I’m not sure it works perfectly. My sense is that it is close though

[Matthias]

@SamMcKay, there are several issues with this ChatGPT code.
But I’d say even if you clean them all up the main flaw remains that MonthNumber is not correctly defined with
Number.RoundUp(Number.From([Attribute]) / 4)

[SoftwareTrain]

let
  Source = Excel.CurrentWorkbook(){[Name = "Production"]}[Content], 
  result = [
    a = List.Transform({Number.From(StartDate) .. Number.From(EndDate)}, Date.From), 
    b = List.Transform(a, each "wk" & Text.PadStart(Number.ToText(Date.WeekOfYear(_)), 2, "0")), 
    c = List.Transform(a, Date.EndOfWeek), 
    d = List.Transform(c, each Date.MonthName(_, "en-GB")), 
    e = Table.FromColumns({b, d}, {"Week", "Month"}), 
    f = Table.Distinct(e), 
    g = Table.UnpivotOtherColumns(Source, {"Product"}, "Attribute", "Value"), 
    h = Table.AddColumn(g, "Custom", each f[Month]{List.PositionOf(f[Week], [Attribute])})[[Product],[Value],[Custom]], 
    i = Table.Pivot(h, List.Distinct(h[Custom]), "Custom", "Value", List.Sum)
  ][i]
in
  result

[dreichert]

let
Source = Excel.CurrentWorkbook(){[Name=“Production”]}[Content],
UnPivot = Table.UnpivotOtherColumns(Source, {“Product”}, “WeekNumber”, “Value”),
ExtractNumberFromText = Table.TransformColumnTypes(Table.TransformColumns(UnPivot, {{“WeekNumber”, each Text.AfterDelimiter(_, “wk”)}}),{{“WeekNumber”, Int64.Type}}),
Date = Table.AddColumn(ExtractNumberFromText, “Date”, each Date.AddWeeks(StartDate,[WeekNumber]-1),type date),
MonthNumber = Table.AddColumn(Date, “MonthNumber”, each Date.Month([Date]), Int64.Type),
MonthName = Table.AddColumn(MonthNumber, “MonthName”, each Date.MonthName([Date])),
WeekDayNumber = Table.AddColumn(MonthName, “DayOfTheWeek”, each Date.DayOfWeek(Date.StartOfMonth([Date]))),
StartingDay = Table.AddColumn(WeekDayNumber, “StartingDay”, each Date.Day([Date])),
#“Personnalisée ajoutée4” = Table.AddColumn(StartingDay, “Month”, each if [MonthNumber] =1 then [MonthName] else
if [DayOfTheWeek] > 2 and [StartingDay] < 7 then Date.MonthName(Date.AddMonths([Date],-1)) else [MonthName]),
#“Colonnes supprimées” = Table.RemoveColumns(#“Personnalisée ajoutée4”,{“Date”, “MonthNumber”, “MonthName”, “DayOfTheWeek”, “StartingDay”,“WeekNumber”}),
#“Colonne dynamique” = Table.Pivot(#“Colonnes supprimées”, List.Distinct(#“Colonnes supprimées”[Month]), “Month”, “Value”, List.Sum)
in
#“Colonne dynamique”

[richard_d]

Answer:
let
Source = Excel.CurrentWorkbook(){[Name=“Production”]}[Content],
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Product”, type text}, {“wk01”, type number}, {“wk02”, type number}, {“wk03”, type number}, {“wk04”, type number}, {“wk05”, type number}, {“wk06”, type number}, {“wk07”, type number}, {“wk08”, type number}, {“wk09”, type number}, {“wk10”, type number}, {“wk11”, type number}, {“wk12”, type number}, {“wk13”, type number}, {“wk14”, type number}, {“wk15”, type number}, {“wk16”, type number}, {“wk17”, type number}, {“wk18”, type number}, {“wk19”, type number}, {“wk20”, type number}, {“wk21”, type number}, {“wk22”, type number}, {“wk23”, type number}, {“wk24”, type number}, {“wk25”, type number}, {“wk26”, type number}, {“wk27”, type number}, {“wk28”, type number}, {“wk29”, type number}, {“wk30”, type number}, {“wk31”, type number}, {“wk32”, type number}, {“wk33”, type number}, {“wk34”, type number}, {“wk35”, type number}, {“wk36”, type number}, {“wk37”, type number}, {“wk38”, type number}, {“wk39”, type number}, {“wk40”, type number}, {“wk41”, type number}, {“wk42”, type number}, {“wk43”, type number}, {“wk44”, type number}, {“wk45”, type number}, {“wk46”, type number}, {“wk47”, type number}, {“wk48”, type number}, {“wk49”, type number}, {“wk50”, type number}, {“wk51”, type number}, {“wk52”, type number}, {“wk53”, type number}}),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Changed Type”, {“Product”}, “Attribute”, “Value”),
#“Split Column by Position” = Table.SplitColumn(#“Unpivoted Other Columns”, “Attribute”, Splitter.SplitTextByRepeatedLengths(2), {“Attribute.1”, “Attribute.2”}),
#“Changed Type1” = Table.TransformColumnTypes(#“Split Column by Position”,{{“Attribute.1”, type text}, {“Attribute.2”, Int64.Type}}),
#“Renamed Columns” = Table.RenameColumns(#“Changed Type1”,{{“Attribute.2”, “WeekStart”}, {“Value”, “Sales”}}),
#“Added Custom” = Table.AddColumn(#“Renamed Columns”, “WeekStartDate”, each Date.StartOfWeek( Date.AddWeeks( #date(2023, 1, 1), [WeekStart] - 1), 1 )),
#“Added Custom1” = Table.AddColumn(#“Added Custom”, “Month”, each Date.MonthName( Date.AddDays( [WeekStartDate], 2 ) )),
#“Removed Columns” = Table.RemoveColumns(#“Added Custom1”,{“Attribute.1”, “WeekStart”, “WeekStartDate”}),
#“Pivoted Column” = Table.Pivot(#“Removed Columns”, List.Distinct(#“Removed Columns”[Month]), “Month”, “Sales”, List.Sum),
#“Reordered Columns” = Table.ReorderColumns(#“Pivoted Column”,{“Product”, “January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”})
in
#“Reordered Columns”