Column header name changes in SQL server therefore error in loading data

[Anonymous]

Hi All, 

Issue: Column header name changes in SQL server, therefore, breaking query steps

My PowerBI query is connected to a SQL server on-premise (Dynamics CRM) and the issue here is that sometimes the column name changes in the database. (for e.g., accountid_name changes to accountidname) thereby breaking my query steps. I am guessing that this happens when Microsoft pushes updates and makes minor changes to the columns in the database. Question is, is there something i can do to prevent this from happening? perhaps i am not modelling my data correctly.... 

Any advice is appreciated. 

Thanks alot

[CNENFRNL · Accepted solution]

As long as the position of the referred column in a table is fixed, say accountid_name is the 4th column of #"Some Table", here's an alternative,

= Expression.Evaluate("#""Some Table""[" & Table.ColumnNames(#"Some Table"){3} & "]", #shared)

[Anonymous]

Would you say this is best practice - is this what you usually do? 

[CNENFRNL]

Not best practice but an alternative for the robust of code in the scanrio you mentioned.

Another point is that the use of Expression.Evaluate() might push you wrap your head around "evaluation context" in the M language.

Enjoy M, enjoy PQ!