How to change data source from Excel to MS SQL Server Database

  • Post category:Power BI
  • Post author:
  • Post published:October 4, 2019

Addend Analytics is a Microsoft Power BI partner based in Mumbai, India. Apart from being authorized for Power BI implementation plan, Addend has successfully executed Power BI projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at or Contact us.

Hi Everyone,

Today, I will be showing you how to change the data source from Excel file to MS SQL Server in Power BI. Let’s get started. 😀

We will change the data source by using some basic M language script. The reason we need to do this by using M language is that Power BI data integration source settings does not provide us an option to change the source directly as shown below.

Power Bi Data


Power BI data source settings do not provide a direct option to change the source to SQL database

Now to change the via M script we will do the following.

1. Go to “Edit Queries” in the ribbon of Power BI.

Power BI Queries


Edit Queries in Power BI

2. To get the M script of SQL server database click on New Source and select SQL Server.

SQL server


Selecting the SQL server as a new source

3. Provide the server name of the database. Specifying the database name is optional.

SQL server database


Providing the Server name of the database

4. Provide the login credentials that you use to log in to the database.

SQL server database


Logging in to the database

5. After logging in successfully you will get a window prompting all the databases and tables that reside into the database so we will select a table from the database and click OK as shown below.

SQL database


Selecting a table that resides in the SQL database

6. Now it will import the selected table, so the next step is to go to the Advanced Editor in the ribbon of Query Editor making sure that we selected the table that we imported from the database as shown below.

Power Bi Query Editor


Advanced Editor in Power Query Editor

7. We will see the M script for the respective table, so copy the M script of the selected section as this contains the data source in M.

highlighted M code


Copy the highlighted M code

8. Click on Done and go to the excel source table that is under Queries.

Power BI Queries


Selecting the Excel table

9. Go to the Advanced Editor of that Excel table it will look like the following.



The highlighted code contains the data source script in M for the excel table

10. Replace the highlighted code with the copied code ending with a comma as shown below.

Power BI Queries


Pasted the copied code of the SQL table.

11. We need to provide the last step name i.e dbo_Quantity as a reference into the next step i.e #”Inserted Year” the first argument that Table. AddColumn function accepts is the name of the last step applied as shown below.



The underlined code is replaced is the change that is made

12. Click on Done

The output of this is shown below.

data source

We will now Close and Apply to check the data source.

data source settings

Now if you see the data source settings you will notice that our data source is changed from Excel to SQL as shown below.

SQL server

Looks great! we have changed the source from Excel to SQL. 😀

If you want a video tutorial of changing the source from Excel to SQL you can have a look at this below.

Hope this helps! 🙂

Anik Bhattacharjee
Addend Analytics