How does Power BI cope with Source Data Changes?

You have a data source that you are connected to with a some nice reports and DAX

However, at some point your data source changes. In this example I have moved from this

To

The question is, what happens to our report when we load in this updated data set?

Straight away when we attempt to Refresh the pbix file we get an error

This would also happen if Power BI Service was being automatically refreshed. We need to go into Power BI Query to get everything sorted

Also we don’t want to have to go and change the DAX, We want to get this resolved with the less amount of effort as possible.

Click on Transform Data

When I click on Refresh All I get the same error

The new columns will need updating in your code so Power BI Understands that they are the same column.

It starts to go wrong here at Changed Type

You can click on this code and Update to the new names. From

= Table.TransformColumnTypes(#”Split Column by Position”,{{“street”, type text}, {“city”, type text}, {“zip”, Int64.Type}, {“state”, type text}, {“beds”, Int64.Type}, {“baths”, Int64.Type}, {“sq__ft”, Int64.Type}, {“type”, type text}, {“sale_date”, type text}, {“price”, Int64.Type}, {“latitude”, type number}, {“longitude”, type number}, {“sale_date – Copy.1”, type text}, {“sale_date – Copy.2”, Int64.Type}})

To

= Table.TransformColumnTypes(#”Split Column by Position”,{{“street”, type text}, {“city”, type text}, {“zip”, Int64.Type}, {“state”, type text}, {“Number of beds“, Int64.Type}, {“Number of baths“, Int64.Type}, {“Square Feet”, Int64.Type}, {“Type of House“, type text}, {“sale_date”, type text}, {“House Price“, Int64.Type}, {“latitude”, type number}, {“longitude”, type number}, {“sale_date – Copy.1”, type text}, {“sale_date – Copy.2”, Int64.Type}})

This section now works

Click on Each Applied Step just to check, in this case, I have resolved all the issues in this one step. Time to refresh and Close and Apply

Annoyingly the DAX is still referencing the original column names

The hope was that the DAX would recognise the column changes

Attempt 2

Lets go back to Power Query Editor and go back to the original code of

= Table.TransformColumnTypes(#”Split Column by Position”,{{“street”, type text}, {“city”, type text}, {“zip”, Int64.Type}, {“state”, type text}, {“beds”, Int64.Type}, {“baths”, Int64.Type}, {“sq__ft”, Int64.Type}, {“type”, type text}, {“sale_date”, type text}, {“price”, Int64.Type}, {“latitude”, type number}, {“longitude”, type number}, {“sale_date – Copy.1”, type text}, {“sale_date – Copy.2”, Int64.Type}})

This sets up the error again.

Add a new applied step before the changed type. What we are going to do is rename the new columns back to the old columns (Bear with me, this seems odd because we don’t want to use the old column names but this will make sense a little later

I click on the Applied step previous to the one that is erroring and rename the column headers in the table

Once done, move to your net step and check they now all work

If you close and Apply your DAX should work again because your column names are now old

But we don’t want to do that. Lets go back to Transform Data

Go to the end of your applied steps and rename your column to the new column names (By clicking on the column header and renaming

And this time when you refresh the data, DAX will accept your changes

So, its a little long winded but mak sure you know exactly what your old column names and new column names are

• Before the error, rename back to old in Power Query
• At the end of applied steps rename back to the new column names
• And you should be in a position where all your reports have accepted the column name changes