Recently, one of my Power BI datasets reported an error.
Based on the refresh history the error seemed obvious enough, the column called ‘CandCUnit’ was missing. However I doubted the veracity of the error as I knew the column existed in the CSV file. I checked the file and yes it still existed.
So what was causing the error. After a couple of fruitless searches I stumbled upon the cause thanks to a post in the Power BI community. The root cause of the problem is the Source step in the Query used to load the data and not the transformation step where the error is reported.
The issue is that whilst the data updates when it is refreshed the structure of the CSV file does not as the structure is statically defined in the Source step:
Source = Csv.Document(Web.Contents("file.csv"),[Delimiter=",", Columns=123, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
In my situation I had added 8 new columns to the file which increased the total number of columns in the file from 123 to 131. The ‘CandCUnit’ column was located in position 123 and the new columns had pushed it position 124. As the query transformation uses column names other columns could still be located based on their name but ‘CandCUnit’ was pushed beyond the defined structure of the file hence it could not be found.
The solution was to edit the Source step using the Advanced Editor to increase the Columns=123 value to the new total of 131.
Source = Csv.Document(Web.Contents("file.csv"),[Delimiter=",", Columns=131, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
This solved the issue 🙂
buildbod.com 