Production demand matrix automatic refresh

Hey Anonymous ,

I assume the error is raised because one of the steps that are applied during the transformation is referencing the column name, that will no longer be present as time moves on. 

You have to be aware of the following setting:
File --> Options and settings --> Options --> Global --> Data load --> Type Detection
I have chosen "Never detect ..."
This choice prevents that Power Query will change data types automatically after specific transformation steps. When I'm done with basic transformations I manually change the data type.

For this reason, the following screenshots of the applied steps may look a little different. The following screenshot shows all the necessary steps:

Here is the M code, taken from the advanced editor:

let
    Source = Excel.Workbook(File.Contents("C:\Users\tmart\OneDrive\Desktop\DynamicDays.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Customer", "Part Number"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

As you can see, just the columns "Customer" and "Part Number" are referenced.
 

I assume that a step like "Change type" is responsible for raising the error as this step references columns by name. If a column is no longer available in the source file, this will raise an error.

I recommend that you remove all steps from your query, change the data type manually after you unpivoted the date columns.

Hopefully, this provides some ideas how to tackle your challenge.

Regards,

Tom