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

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.

Code

 

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.

Code

 

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
https://www.addendanalytics.com/

 

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central 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 or contacting us.