Column name changes in the data source errors in DAX

[DebbieE]

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

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

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 this error 'The column table beds was not found'

I go into Power BI Query to get everything sorted

I don't want to have to go and change the DAX, I 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 the 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

I 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 my DAX is still referencing the original column names

The hope was that If I updated the cod in Power Query then this would update accordingly. Is there a better way of doing this because this has created a lot of work in updating all the measures?

The hope was that the DAX would recognise the column changes

[cpearson · Accepted solution]

If you move to the step before Changed Type (i.e. before it breaks) and insert a step to rename the column from the new name (Number of Beds) back to your old name (Beds) that should result in the later steps working and the references to that field should then be recognised too.

[DebbieE]

---

@cpearson wrote:

If you move to the step before Changed Type (i.e. before it breaks) and insert a step to rename the column from the new name (Number of Beds) back to your old name (Beds) that should result in the later steps working and the references to that field should then be recognised too.

---

Would that mean that Im reverting everything to the old names though? I dont want to do that. I do want to use the new names

[cpearson]

Yes, it would use the old names, I thought you were looking for a quick and easy way to fix your report when your source data had changed field names.

The question is where do you want to see the new names and how much time do you want to invest to change it? 

If the fields in your data source have changed names then you can either configure it to change them back to the original, so that all the work you've done downstream can remain intact, or you can work through all the errors to change each step to the new names. Or alternatively you could fudge the data load by changing them back to the original names, and then change the names of the fields/metrics in the visual side (but this will add confusion/complexity).

Also, regarding "Yes, Column Not found. Its looking for Bath not Number of Baths"... the step I suggested would fix one field "Beds" you'd need to repeat that for all the newly named columns.

[DebbieE]

It still seems a bit of an odd workaraound to rename from new to old and then back to new but I have documented it and it works

https://debbiesmspowerbiazureblog.home.blog/2020/02/20/how-does-power-bi-cope-with-source-data-chang...

[parry2k]

@DebbieE then change column name in PQ what it is looing for, not sure where is the issue

[DebbieE]

---

@parry2k wrote:

@DebbieE then change column name in PQ what it is looing for, not sure where is the issue

---

The isssue was in my initial post. It seems the fix is to rename everything back to the old column names before all the applied steps.

Then to rename to the new column names at the end of all the applied steps and DAX will then use the new column names

The issue was, simply fixing the step that stopped working to the new column names and applying, DAX would still be looking for the old column names

[parry2k]

@DebbieE exactly, DAX doesn't care as far as the name is the name you might have tons of transformation on the way but if you are returning correct column name (as per you original table) than DAX should work Anyhow seems like you are aleady on the right track.

Would appreciate Kudos 🙂 if my solution helped.

[parry2k]

@DebbieE DAX should work, what error you are getting in DAX? Column not found

[DebbieE]

Yes, Column Not found. Its looking for Bath not Number of Baths