How can I properly use variable month columns in a PowerBI query?

I have a performance monitoring table in which users are listed vertically and then months are listed across the header. These months are dynamically generated on a 12-month rolling window. At the beginning of each month, one month falls off the back of the query and another appears at the front. After beginning of month, I get the following error until I manually re-run the report: The '<#MONTH>' column does not exist in the rowset. Where '<#MONTH>' is the month that gets dropped off, e.g. if it is Sept 2019, it would be 'August 2018'.

I tried adding a window to the query that moved the start of the query ahead a day to try to eliminate the perceived race condition. This did not work.

Here is the M query I have currently:

let
    Source = US_TOTALS,
    #"Appended Query" = Table.SelectRows(Table.Combine({Source, CA_TOTALS}), each [DATELASTFULFILLMENT] >= #"This Year"),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"SALESMAN", "RegionName", "DATELASTFULFILLMENT", "Total Sales", "Customer", "GROUP"}),
    #"Inserted Start of Month" = Table.AddColumn(#"Reordered Columns", "StartOfMonth", each Date.StartOfMonth([DATELASTFULFILLMENT]), type date),
    #"Reordered Columns1" = Table.ReorderColumns(#"Inserted Start of Month",{"SALESMAN", "RegionName", "DATELASTFULFILLMENT", "Total Sales", "StartOfMonth", "GROUP", "Customer"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"Customer"}),
    #"Date One" = Date.AddMonths(Date.StartOfMonth(Date.AddDays(DateTime.Date(DateTime.LocalNow()),1)),1),
    #"Date Two" = Date.AddYears(Date.AddMonths(Date.StartOfMonth(Date.AddDays(DateTime.Date(DateTime.LocalNow()),1)),0),-1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [DATELASTFULFILLMENT] < Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),1) and [DATELASTFULFILLMENT] >= Date.AddYears(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),0),-1)),
    //#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Date.From([DATELASTFULFILLMENT]) < #"Date One" and Date.From([DATELASTFULFILLMENT]) >= #"Date Two"),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"SALESMAN", "RegionName", "StartOfMonth", "GROUP"}, {{"Total", each List.Sum([Total Sales]), type number}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Grouped Rows",{"SALESMAN", "RegionName", "GROUP", "StartOfMonth", "Total"}),
    #"Inserted Month Name" = Table.AddColumn(#"Reordered Columns2", "Month Name", each Date.MonthName([StartOfMonth]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([StartOfMonth]), type number),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Month"),
    #"Removed Columns2" = Table.RemoveColumns(#"Merged Columns",{"StartOfMonth", "GROUP"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns2", {"SALESMAN", "Month", "RegionName"}, {{"Total", each List.Sum([Total]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"SALESMAN", Order.Ascending}}),
    #"Pivoted Columns" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Month]), "Month", "Total", List.Sum),
    Columns = List.RemoveFirstN(Table.ColumnNames(#"Pivoted Columns"),2),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Columns",null,0,Replacer.ReplaceValue,Columns),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",List.Transform(Columns, each {_, Currency.Type })),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"SALESMAN"},USER_MAPPING_COMBINED,{"USERNAME"},"USER_MAPPING_COMBINED",JoinKind.LeftOuter),
    #"Expanded USER_MAPPING" = Table.ExpandTableColumn(#"Merged Queries", "USER_MAPPING_COMBINED", {"NAME"}, {"NAME"})
in
    #"Expanded USER_MAPPING"

Expected Results: The query refreshes as normal

Actual Results: The query errors with The '<#MONTH>' column does not exist in the rowset. Where '<#MONTH>' is the month that gets dropped off, e.g. if it is Sept 2019, it would be 'August 2018'.

screenshot for reference:

Solution

Ok, I'm going to take a second swing at this. If I'm way off base, I apologize. But let me bring out an example to simulate your error.

let
    Seed = Number.Mod(Number.Round(Time.Second(DateTime.LocalNow())), 7) + 1,
    Source = List.Generate(()=> Seed, each _ < Seed + 6, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"MonthNumbers"}, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "MonthNames", each Date.MonthName(#date(2019, [MonthNumbers], 1))),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[MonthNames]), "MonthNames", "MonthNumbers", List.Sum)
in
    #"Pivoted Column"

So, this creates a table that looks like so:

But every single second, the frame shifts by a month. So if you refresh the preview every second, you'll see the frame slide Jan-Jun, Feb-Jul, Mar-Aug... when December is the last month in the window, it skips back to the Jan-Jun. The point is, the columns are changing.

Now you try to load this model. It does not work!

From the time you create the model with one column set, to the time it takes to load the column set, those columns have been changed and so one of the columns isn't there any more. This is like when your month changes. When you go in and load manually, you'll update your model and things will work fine until the next change in columns. But when you're doing it with the scheduled load, that doesn't update the model, it just tries to load the data and runs into this column mismatch.

So, how do we fix it without losing this dynamic naming? Let's look at that pivot... what if we don't do it and leave our power query looking like this?

Now the column names won't change when we load it into the model. We create a matrix visualization like so, and do some refreshes:

No errors, nice dynamic headers.

So, that's the approach that I think you need. I hope it helps.