The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
@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
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.
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
@DebbieE then change column name in PQ what it is looing for, not sure where is the issue
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@DebbieE DAX should work, what error you are getting in DAX? Column not found
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes, Column Not found. Its looking for Bath not Number of Baths
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
139 | |
110 | |
107 | |
76 | |
62 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |