How to Change Data Source from SQL Synapse to Kusto Without Affecting Relationships in Power BI

Hi AnupTandale 

To change your data source from SQL Synapse to Kusto (Azure Data Explorer) without affecting existing relationships in your Power BI model, follow these steps:

Recommended Approach

1. Use Power Query Editor to Update the Source

1. Open Power Query Editor (Home > Transform data)

2. Locate the table you want to modify

3. Right-click the "Source" step in the Applied Steps pane

4. Update the connection details to point to your Kusto cluster instead of Synapse

2. Preserve Column Structure (Critical for Relationships)

• Ensure the new Kusto query returns exactly the same column names and data types as your original Synapse query

• Verify that:

  • Key columns used in relationships have identical names

  • Data types match (especially for join columns)

  • Column order remains the same

3. Alternative Method - Parameterize the Source

1. Create parameters for connection details:

   // In Power Query Editor > Manage Parameters
   KustoCluster = "your-cluster-name",
   KustoDatabase = "your-database-name"

2. Modify your source step to use the parameters:

   Source = Kusto.Contents(KustoCluster, KustoDatabase, "YourTable")

Troubleshooting Relationship Issues

If relationships break after changing the source:

1. Check for hidden changes:

   • Verify no columns were inadvertently renamed

   • Confirm no data type conversions occurred

2. Re-establish relationships manually if needed:

   • Go to Model view

   • Drag between key fields to recreate relationships

   • Verify cardinality and cross-filter direction match the original

Best Practices

1. Make a backup of your PBIX file before making changes

2. Test with one table first before converting all data sources

3. Document your relationships beforehand for easy verification

4. Consider using calculation groups if you need to maintain compatibility with both sources during transition

Common Pitfalls to Avoid

• Changing column names or data types during migration

• Altering the query structure significantly (adding/removing columns)

• Forgetting to update authentication credentials for the new source

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

Hi AnupTandale 

Thankyou  ajaybabuinturi  Elena_Kalina  Ritaf1983  for taking time and providing solution.

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Akhil.

Hi AnupTandale 

Just checking in to see if the solution we shared worked for you? If you're still facing any issues or need more help, feel free to let us know, we're happy to assist further.

If the response helped resolve your issue, please consider marking it as Accepted as Solution and give it a thumbs up if you found it helpful.

Thanks,
Akhil.

Hi AnupTandale 

Just checking in one last time. Were you able to try out any of the suggestions shared earlier? If your issue is resolved, marking the accepted solution would be a big help to others who might be facing the same scenario.

If you went in a different direction or still need support, feel free to drop a quick update, we’re happy to keep helping.

Regards,
Akhil.