Recently, one of my Power BI datasets reported an error.

refresh error
One of your columns is missing…

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.

column exists
Hmm but it exists…

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.

query error
This step is not the culprit

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 🙂

 

 

 

 

 

Tagged

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s