Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 tabl...
Need Help
  • Cookistador's avatar
    Cookistador
    1 year ago

    Hello,
    The first thing is to make a mapping of your table name and field

    For 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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 reply

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi 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.