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:

1. Column names remain unchanged,
2. Replicate all the data transformations under Applied Steps,
3. 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:

Select & Copy the M code from the Advance Editor

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:

Paste the code in the SQL query’s Advance Editor after adding a comma(,)

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.

For more great content, check out the Resource Centre

About the Author:

Vivek is a data enthusiast who works on Excel, Power BI, Power Apps, SQL Server, and SharePoint. He is mostly a self-taught person and loves to share his knowledge. This inspired him to leave his full-time job and start working as a BI Consultant & Trainer. He first saw Power BI approx. five years ago, and it was love at first sight. He prefers to upgrade himself to the latest BI technologies and best practices during his free time, writes blogs, and answer questions on the Power BI community. He loves traveling and has covered over 25,000 kilometers on a motorcycle with his wife in India. He loves trekking Himalayas, an avid reader (primarily books, but blogs also do), an amateur photographer, and plays guitar. Apart from this, he is quite a dull person.

Ranjan, V. (2021). Power BI: Changing Data Source from Excel to SQL. Available at: https://www.vivran.in/post/power-bi-changing-data-source-from-excel-to-sql [Accessed: 1st April 2021].