Forum Discussion
How to change data source from Excel to SQL Server
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.
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 ?
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