I would like to automate the column name change error in Power BI desktop.
To explain it more precisely, I have a excel source which is connected to Power BI desktop and created a report out of it. Now, after a certain period of time one of the column name got changed (assume : Name-> Firstname). When I do the data refresh it throws below error and it is expected, but my requirement is to automate this step.
Because the column name change is happening so often and I am looking for a way to automate it. I am expecting the result in Table or Matrix visual. Column name should change dynamically whenever there is a change in column name in the source table.
If your column name is changed in PowerQuery the name change should flow through to your visual level table/matrix headers, and any measures etc. referencing that column. You just need to resolve your query load errors caused by any Power Query transformation steps referencing the column by name.
Alternatively, if you DON'T want the column name to change in your visuals you could either create a calculated column in PBI that references the column with the non static name and use that as your column in your visual instead. Or you need to find a way to rename the column in PowerQuery which doesn't rely on referencing the column by name. One other way to do that would be:
I do not want to do anything neither in M code nor in visual. I there is any change in source file column name that should reflect in Power BI Table/Matrix visual.
It shouldn't if you're applying the transformations based on the column index rather than the column name, as in that example. But of course if the column order in your data source changes over time that will not work.
The error wont actually be caused by the column name changing, but by any transformation steps applied to the column which by default reference the column name.
You should review the transformations applied to the column and identify which ones are causing the error.
If your column layout is consistent, you could try editing the M code for your transform steps to reference the column by it's index instead of the column name.
But It still need manual intervention when there is a change in column name. How can we automate it ?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.