I have two files, a History file and a Forecast file. Each month, some of the column names in the files change based on a rolling monthly basis… for example, the forecast file has 4 static columns (sku, description, country, location) and then 28 months of forecasted sales data with column headers designated as the month 11/1/2021 through 1/1/2024….next month when I get the data file, the months will change to 12/1/2021 and end in 2/1/2024…the number of columns is always the same just the months roll over. The history file will be the same format as well with 24 months back rolling as the months go by…
important to note that the files themselves are in very different formats (one comes horizontal, other is vertical, the sku and descriptions come in one column in one of the files)…basically I’m finding it very hard to manipulate the file without referring to the “hard coded” column names. The issue is the query breaks when I update it next month because the previous month’s column name disappears and causes a break in the code.
I’m unable to create a power query to combine both files with dynamic column header names. I’ve tried a couple of options include creating lists but I keep getting errors so clearly I’m missing some critical part of the process…
There are lots of videos online to solve this issue but I have not been able to implement a single one successfully without an error. Other options are not practical because they deal with changing just one columns, while my files have 30+ columns each.
I am trying to implement a solution outlined in this video: YouTube video :
The idea is to replace the column names in the source file with the columns names in a mapping table.
The issue I am facing is that I am basing my query not from an external file source but a named range in the same file. I have my mapping table but when I follow the steps, I get the following message: “Expression.Error: a cyclic reference was encountered during evaluation”
list.Accumulate(Table.ToRecords(Mapping) , _ ,
(state, current)=> Text.Replace(Text.Upper(state),current
My named range and query name is “tbl_Collab” …if I understand the error correctly it’s because the step in the query can’t reference itself but not sure how to overcome this issue….when I replace the “tbl_Collab” in the code with the previous step, all that happens is the column names end up in upper case, so it seems to transform it but doesn’t look at the “after” column to look up and replace the values.
Any help would be greatly appreciated.