Forum Discussion
Changing Power BI Data Source to Migrated Dataverse Tables
Hi,
I hope you're all doing well. My company recently migrated the backend data of an existing Power BI dashboard from on-premise SQL storage to Microsoft Dataverse. During this migration, some table names and column names were changed.
I need to reconnect the existing Power BI dashboard to the new Dataverse tables while ensuring that all visuals, measures, queries, and relationships remain intact without errors.
What is the best approach to update the data source and align the schema without breaking the current report? Any guidance or best practices would be greatly appreciated!
Thank you in advance for your help.
Best Regards,
Musthakeem
Hello,
The first thing is to make a mapping of your table name and fieldFor example:
SQL Table: Customers -> Dataverse Table: Accounts
SQL Column: CustomerID -> Dataverse Column: accountid....
Connect to dataverse as a datasouce and, then create a copy of the first query you want to update and modify the connector with advance editor (modify the source and not the copied, the copied source is just a back up)
It should like
Source = Sql.Databases("YourSQLServer", "YourDatabase"), YourTable = Source{[Name="YourTable"]}[Data], #"Renamed Columns" = Table.RenameColumns(YourTable,{{"CustomerID", "OldCustomerID"}})Source = CommonDataService.Database("YourDataverseEnvironment.crm.dynamics.com"), YourTable = Source{[Name="accounts"]}[Data], #"Renamed Columns" = Table.RenameColumns(YourTable,{{"accountid", "OldCustomerID"}})Once everything is imported use the "Rename Columns" transformation to align the new Dataverse column names with the old SQL column names. This is crucial for maintaining relationships and measures.
Once that is done, you can close and apply, if you have error message, reopen power queyr and continue to fix
Do not hesitate to ask if you need more details for a specific point, it is a general approach
6 Replies
Hello,
The first thing is to make a mapping of your table name and fieldFor example:
SQL Table: Customers -> Dataverse Table: Accounts
SQL Column: CustomerID -> Dataverse Column: accountid....
Connect to dataverse as a datasouce and, then create a copy of the first query you want to update and modify the connector with advance editor (modify the source and not the copied, the copied source is just a back up)
It should like
Source = Sql.Databases("YourSQLServer", "YourDatabase"), YourTable = Source{[Name="YourTable"]}[Data], #"Renamed Columns" = Table.RenameColumns(YourTable,{{"CustomerID", "OldCustomerID"}})Source = CommonDataService.Database("YourDataverseEnvironment.crm.dynamics.com"), YourTable = Source{[Name="accounts"]}[Data], #"Renamed Columns" = Table.RenameColumns(YourTable,{{"accountid", "OldCustomerID"}})Once everything is imported use the "Rename Columns" transformation to align the new Dataverse column names with the old SQL column names. This is crucial for maintaining relationships and measures.
Once that is done, you can close and apply, if you have error message, reopen power queyr and continue to fix
Do not hesitate to ask if you need more details for a specific point, it is a general approach
Hi Anonymous
I wanted to check if you had the opportunity to review the information provided. by Cookistador Please feel free to contact us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Shamil,
Sorry for the delayed reply. The description above outlines the task I was assigned. However, due to a technical issue, I haven't been able to access the new data source and the report to verify whether the proposed solution works.
I was researching possible solutions to the issue, but since I couldn't find any online, I posted my question here. I'll let you know if the solution works as soon as I gain access.
Once again sorry for the late replyHi Anonymous
Thanks for getting back and sharing the update!
Technical access issues can definitely slow things down. I appreciate you taking the time to explain.
Once you’re able to access the new data source and test things out, feel free to drop an update here. I’ll be happy to revisit and help further if needed.
Thank you.