Automate column name change error in Power BI desktop

[Arul]

Hi All,

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.

Thanks,

Arul

[halfglassdarkly]

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:

• Depromote headers (so all your column names will just be named Column1, Column2, etc.)
• Rename each column manually and the Power Query transform step will reference the column by its depromoted name (e.g. Column1) which won't change if your underlying data does.
• Remove top 1 row to get rid of your old headers.

[mahenkj2]

hi @halfglassdarkly 

Found one other way useful!

[Arul]

@halfglassdarkly ,

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.

Thanks,

Arul

[halfglassdarkly]

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.

[Arul]

@halfglassdarkly ,

Yes, but I need to automate it and this change need to be at visual level in Table or matrix.

Thanks,

Arul

[halfglassdarkly]

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.

see: https://stackoverflow.com/questions/50113354/how-to-refer-columns-in-power-query-by-index-or-positio... 

[nhathuoctinhyeu]

thank you

[Arul]

@halfglassdarkly ,

But It still need manual intervention when there is a change in column name. How can we automate it ?

Thanks,

Arul