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 the following.
1. Go to “Edit Queries” in the ribbon of Power BI.
2. To get the M script of SQL server database click on New Source and select SQL Server.
3. Provide the server name of the database. Specifying the database name is optional.
4. Provide the login credentials that you use to log 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.
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.
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.
8. Click on Done and go to the excel source table that is under Queries.
9. Go to the Advanced Editor of that Excel table it will look like the following.
10. Replace the highlighted code with the copied code ending with a comma as shown below.
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.
12. Click on Done
The output of this is shown below.
We will now Close and Apply to check the data source.
Now if you see the data source settings you will notice that our data source is changed from Excel to SQL as shown below.
Looks great! we have changed the source from Excel to SQL.
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.