Migrating data sources at times can be tricky in Power BI. This article focuses on how to change the data source from an Excel table to a SQL table in minimum possible steps, considering the following scenarios:
The name and structure of the data source tables are different in Excel and SQL. It includes a change of column names.
Migrate and apply all the data transformation steps.
The migration should not affect the existing data model and the visuals present in the report.
To achieve this, we require to ensure three points:
Column names remain unchanged,
Replicate all the data transformations under Applied Steps,
Query Name remains unchanged.
For our example, we are changing the data source of the Product Subcategory table from Excel to SQL:
Excel table structure:
SQL table structure:
Step 1: Bring the data from SQL Server using the native query.
It ensures that we have the same column names:
In case you can't write the native query to change the column name, then add a step in Power Query to change the column names.
Step 2: Replicate the data transformation steps using Advance Editor
This step is the trickiest of the three but not difficult.
Step 1: Copy the data transformation steps from the Advance Editor of the Excel table
Select the query > Home > Advance Editor and copy steps, which include the data transformations. In this example, we have applied two transformations:
We can find these steps in the Advanced Editor under the same names:
Step 2: Paste the copied code from the previous step to the SQL query Advance Editor
Add a comma after the last line, and paste the copied code:
Update the reference of the previous step. In this case, the name of the last step is “Source”.
Step 3: Delete the Excel Query and Rename the SQL Query
Delete the Excel query and assign the same name to the SQL query:
And this should do.