Scheduled Refresh Error on Dynamic Column Names of Data source tables

[PBIUWO]

Hi,

I created a Power BI that contains a table that has the column headers change every month as it contains the most recent 12 month. 

I can refresh this fine on the Power BI desktop, but when it comes to the Scheduled Refresh, when a new month comes, it says it's unable to refresh because it can't find the column name. 

Does any no how to go around this issue?

Thank you

[v-deddai1-msft]

Hi @PBIUWO ,

Have you solved the issue? Would you please show us the solution of it if possible? 

Best Regards,

Dedmon Dai

[parry2k]

@PBIUWO that is calling a function with that name, you need to send the script of that function.

[PBIUWO]

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

[parry2k]

@PBIUWO what is happening in this function:

#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),

[PBIUWO]

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. 

[parry2k]

@PBIUWO well not sure how you are doing it but clearly it shows it is expecting that column and you have to check your steps and see where it is failing, very hard to tell without looking at the M code.

[PBIUWO]

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"

[parry2k]

@PBIUWO does the structure of the file change or it remains the same. It is weird that will work in Desktop and not in service?

[PBIUWO]

@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).