How to change data source from Excel to SQL Server

[theBIA]

 Hi All,

I am curious as to how I can essentially subsititute my data sources from excel files to a connection to a SQL server ?

Currently I am pulling my data from 4 excel files, and I wish to connect my dashboard to a server. These excel reports are extracted from the same SQL server I wish to connect to. Thus I am curious as to how to replace my excel files with the query for the server ?

Previously, I have simply moved my excel file from one folder to another and the whole dashboard broke. So looking at a method to keep the dashboard intact, while switching the data source only.  

TIA 🙂

[tex628]

I would recommend that you start by creating a new query in the same report, and navigate to the correct table in your SQL. Continue untill you have the exact same data in your SQL query as you have in your excel file. When you do will will eventually be able to simply replace the source of your original query without having to change anything else. 

I would recommend doing this in a copy of your report.

[theBIA]

Hi thank you for your response.

Please could you clarify the following;

When you mentioned create the query in the report do you mean the powerBI report? Is the a built in console ?

And when it comes to changing the source, would it be as simple as opening the query editor and going to the source query step and changing it ?

[tex628]

Make a copy of your current Pbix file, go into the query editor and select get data. Choose the SQL connector and navigate to the correct tables. 

When your SQL is identical to the Excel query you should be able to go into the advanced edior on your excel query and simply change the source from the excel file to the SQL query. 

This will mean that your excel query will now read from the SQL query instead of the excel file. If everything has been done correctly it should run just as it did previously. 

If you rightclick on the SQL and click on 'reference' you will get a new query that is a reference to the SQL query. If you then go into the advanced editor on the reference query you can find the syntax there that you are going to need to add to your excel query.

Hope this shreds some light, just let me know if you have any issues! 

/ J