Scheduled Refresh Error on Dynamic Column Names of Data source tables

parry2k  

File Name, and structure is the same.

How i structrued the data source is, the source is pointing to the position of the columns, rather than the name of the columns. 

On the Refresh, the errors point that it's missing the column name (Ex. Column 2019-11 does not exist in the rowset). 

let
Source = Folder.Files("SOME SOURCE"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", type number}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", Int64.Type}, {"Column38", Int64.Type}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", Int64.Type}, {"Column42", Int64.Type}, {"Column43", Int64.Type}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", Int64.Type}, {"Column48", Int64.Type}, {"Column49", Int64.Type}, {"Column50", Int64.Type}, {"Column51", Int64.Type}, {"Column52", Int64.Type}, {"Column53", Int64.Type}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",2),
#"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Item Code"}, {"Column2", "System Code"}, {"Column3", "Item Description"}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Renamed Columns", {"Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Sales QTY"}}),
#"Removed Blank Rows" = Table.SelectRows(#"Renamed Columns2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"

It's adding a new column called "Transform File (2)" and that cell value is the table. 

So if I expand it, it contains the table with all of the data. 

I was able to fix it when I just readded the data and the script.