Hello,
Hoping to please resolve the following PQ error, when I refresh the data:
Expression.Error: The column '14/01/2022' of the table wasn't found.
Details:
14/01/2022
The data is refreshed each week with a new file, which has a different number of columns from the previous file -- because new dates are added to the new file.
Is there a way for PQ to automatically include the additional columns, without having to manually type in the new column names in the advanced editor? And then apply the same steps to the new columns (e.g. formatting, etc.)?
Attached is an example of the original v. new columns, and the Advanced Editor code.
Thanks, Danielle
Hi Danielle,
First, you should remove the "Columns=39," from the source line to allow for variable number of columns. At the moment it will only ever pick up the first 39 columns.
i.e.
Source = Csv.Document(File.Contents("\xxxx Report.csv"),[Delimiter=",", Columns=39, Encoding=1252, QuoteStyle=QuoteStyle.None]),
Should become:
Source = Csv.Document(File.Contents("\xxxx Report.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
Next, this data should be unpivoted. i.e. you shouldn't have column names that are dates. You should have a date column and then other columns that contain fixed names.
If that is not practical, this post explains some approaches to dynamically referencing columns by number or generating a list of column names that you can then work with. If you need help, please upload a sample CSV file.
Mynda
Thanks, Mynda.
Would you be able to please provide a link to post that explains dynamically referencing columns by number or a list of column names?
Oops, sorry! I forgot to insert the link. I've edited the post above and the link is there now.
