PowerBI: PBIX file change after migrating from Data Lake connector to Synapse Link for Dataverse?

[IanWaring]

We have been using the Data Lake connector on Dynamics 365 Finance for a year and are just about to move to using Synapse Link for Dataverse. While all the same tables are available to our PowerBI models, there is a breaking change as the data table names and field names have gone from mixed case (with the Data Lake connector) to lower case only (on Synapse Link for Dataverse). Given DAX is case sensitive, this is a breaking change for PowerBI users.

Is there any utility or simple method of getting all the afflicted table names and field names in a PBIX file changed to their new lower case equivalents?

[bsheffer · Accepted solution]

This is why for all new datasources I add mcode to force the case to upper or lower (your preference)

Just add a step after your source step and use this code:

= Table.TransformColumnNames( Source, each Text.Upper( _ ) )

Or

= Table.TransformColumnNames( Source, each Text.Lower( _ ) )

That prevents changes to the case of the source table or view from affecting your datasource because of case.

Doing this to an existing datasource may break the reports that reference it because it will see any change in case as a new field

[bsheffer · Accepted solution]

This is why for all new datasources I add mcode to force the case to upper or lower (your preference)

Just add a step after your source step and use this code:

= Table.TransformColumnNames( Source, each Text.Upper( _ ) )

Or

= Table.TransformColumnNames( Source, each Text.Lower( _ ) )

That prevents changes to the case of the source table or view from affecting your datasource because of case.

Doing this to an existing datasource may break the reports that reference it because it will see any change in case as a new field

[bsheffer · Accepted solution]

your alternative (if you are using a select for your table source) is to use an explicit field list which will force the case of the field.

instead of select * from source use select Merchant_Name, TOTAL_SALES from source to enforce the case you want for your fields.

[IanWaring]

Smiling - as you should see the number of fields your average table in D365 Finance shovels over the wall!

[lbendlin]

Given DAX is case sensitive, this is a breaking change for PowerBO users.

DAX is not case sensitive. Power Query is case sensitive.

[IanWaring]

Feedback accepted - but the PBIX files built using Data Lake remain unusable until you edit every mixed case table and field name to lower case, which is a considerable amount of no-value work. Hopefully there's a more productive way to fix this rather than changing everything or starting from stratch.

[lbendlin]

Agree, this should not be necessary, it is extra idle work.  If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[IanWaring]

(thinks - we have Premier Support in place, but thought that was for defects only)

[lbendlin]

what do you call this if not a defect?

[IanWaring]

Defect but acknowledged as a breaking change by the dev team in Seattle - so will park things there. I appear to be a lonely voice but it looks like it's easier to rebuild from scratch, not least as some of the enumerated fields no longer need multiple joins to extract their meaning (eg: vendor or customer account numbers to their names).

I really appreciate the help. Thank you.