{"id":3323,"date":"2021-05-08T09:55:20","date_gmt":"2021-05-08T16:55:20","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3323"},"modified":"2021-05-08T09:55:21","modified_gmt":"2021-05-08T16:55:21","slug":"how-to-change-data-source-from-excel-to-ms-sql-server-database-in-power-bi","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3323","title":{"rendered":"How to Change Data Source From Excel to MS SQL Server Database in Power BI"},"content":{"rendered":"\n

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 source settings does not provide us an option to change the source directly as shown below.<\/p>\n\n\n\n

\"\"
Power BI data source settings do not provide a direct option to change the source to SQL database<\/figcaption><\/figure>\n\n\n\n

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

1. Go to \u201cEdit Queries\u201d in the ribbon of Power BI.<\/p>\n\n\n\n

\"\"
Edit Queries in Power BI<\/figcaption><\/figure>\n\n\n\n

2. To get the M script of SQL server database click on New Source and select SQL Server.<\/p>\n\n\n\n

\"\"
Selecting the SQL server as a new source<\/figcaption><\/figure>\n\n\n\n

3. Provide the server name of the database. Specifying the database name is optional.<\/p>\n\n\n\n

\"\"
Providing the Server name of the database<\/figcaption><\/figure>\n\n\n\n

4. Provide the login credentials that you use to log in to the database.<\/p>\n\n\n\n

\"\"
Logging in to the database<\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"
Selecting a table that resides in the SQL database<\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"
Advanced Editor in Power Query Editor<\/figcaption><\/figure>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"
Copy the highlighted M code<\/figcaption><\/figure>\n\n\n\n

8. Click on Done and go to the excel source table that is under Queries.<\/p>\n\n\n\n

\"\"
Selecting the Excel table<\/figcaption><\/figure>\n\n\n\n

9. Go to the Advanced Editor of that Excel table it will look like the following.<\/p>\n\n\n\n

\"\"
The highlighted code contains the data source script in M for excel table<\/figcaption><\/figure>\n\n\n\n

10. Replace the highlighted code with the copied code ending with a comma as shown below.<\/p>\n\n\n\n

\"\"
Pasted the copied code of the SQL table.<\/figcaption><\/figure>\n\n\n\n

11. We need to provide the last step name i.e dbo_Quantity as a reference into the next step i.e #\u201dInserted Year\u201d the first argument that Table.AddColumn function accepts is the name of the last step applied as shown below.<\/p>\n\n\n\n

\"\"
The underlined code is replaced is the change that is made<\/figcaption><\/figure>\n\n\n\n

12. Click on Done<\/p>\n\n\n\n

The output of this is shown below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We will now Close and Apply to check the data source.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now if you see the data source settings you will notice that our data source is changed from Excel to SQL as shown below.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Looks great! we have changed the source from Excel to SQL.\u00a0<\/p>\n\n\n\n

Ref: https:\/\/addendanalytics.com\/how-to-change-data-source-from-excel-to-ms-sql-server-database\/#:~:text=Go%20to%20%E2%80%9CEdit%20Queries%E2%80%9D%20in%20the%20ribbon%20of%20Power%20BI.&text=2.,Source%20and%20select%20SQL%20Server.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 source settings does not provide us an option to change the source directly as shown below. Now to change the via M script we will do Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[720,721],"class_list":["post-3323","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-change-data-source-from-excel-to-ms-sql","tag-change-data-source-from-excel-to-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3323"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3323\/revisions"}],"predecessor-version":[{"id":3324,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3323\/revisions\/3324"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}