Data Bear’s Monday Motivation Blog Post

Today’s post is perhaps more for the beginner in Power BI, how to troubleshoot file changes in Power BI.  This specifically refers to excel and CSV file changes.

These changes could be any of the following:

1. The file was moved from its original location
2. A column was added/removed
3. Column name changed

How to troubleshoot file changes after the below instances

1. File was moved

When you need to troubleshoot file changes, this is the most common issue, we all move files around in our drives all the time, but with Power BI, this is obviously an issue, but very easy to fix.

The error you will receive is this.

All you have to do is direct your report to the file in the new location.

Go to the Transform Data option.

Navigate to the correct table.

Go to the source and browse to the new location, and that’s it.

2. A column was added/removed

Adding a column:  This can sometimes cause issues, but not always, if a new column was added you should simply be able to refresh your data and the column will be visible.  BE CAREFUL, if you have done some extreme power query on the table, like unpivot/pivot (post) the data, it may distort and mess this up big time.  Extra post on adding a custom column here.

Removing a column:  Again if you have not performed any transformations on this column or used it in a visual, you may not experience problems, but say you have.  In my example, I did just one transformation (Capitalize Each Word) on my column.

I then went to my excel sheet and removed that column.  Saved and closed it and went back to my report and pressed refresh.  I get the below error.

Go to Transform data

You will immediately see you need to troubleshoot file changes as these red striped bars, indicating an error appears on each column.  Press Refresh Preview and the error will be clear.

Go to the first step on your Applied steps panel, and work your way down until the error occurs.  In my example, it’s on the last step, and I could simply delete that step.  BE CAREFUL, if your step is not the last one, all subsequent steps may be influenced depending on what you are deleting, I would suggest saving the file before attempting this.  Deleting a step is permanent, undo is not available.

3. Column name change

In my example I have a Countries column, I did some transformation in my table that specifically relates to that column.  Then I went to my excel sheet and changed the column name to Country.

Tried to refresh the report, you will get the below error again.

This time most of the steps will return an error.  You will have to change the column name in the Advanced Editor section.  For each step and instance that the old column name is referenced, you will have to change to the new.  If your code here is very long, you can copy and paste into a notepad text file and fine and replace it.  BE CAREFUL the smallest change like a comma or parenthesis will break it and is very difficult to troubleshoot.

This will fix your table, but not necessarily your visual, you may need to replace the field giving you an error with the newly renamed field.

Note that this solution is best suited when a column name has been changed indefinitely.  In some instances, you can expect a column name to change frequently, for those instances another approach should be taken, view this post for more information on that.

I hope this helps you “fix” your broken report and have it back to working in no time.

Training option here.