Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryan87
Regular Visitor

Refreshing MS query data automatically

I have a number of MS query/SQL queries in excel files that I would like to visualise using BI and configure so that they refresh autoamtically. I have setup a personal gateway to my dataset in BI and the scheduled refreshes are running succefully however the actual data itself in the query isn't refreshing as part of this refresh. I still have to manually open the excel files and refresh the queries before the scheduled refresh takes place to pull through the latest data.

 

I have tried also setting up an automatic refresh in excel however this relies on the file being open and then saved once refreshed. I don't want to go down the route of VBA code in excel as that what we are currently doing outside of BI with files on our server to display live reports around the business but it isn't stable enough.

 

Is there a way to set up the scheduled refresh so that it updates the query file, then updates the report and then updates the embed URL code as a result wihtout having to touch any of the files? I initially created the report using the BI desktop app (get data option) and publishing it to the web, which I have since re-done with the same result. I have also tried an on premise gateway but still no joy.

 

Do you have any ideas?

 

Thanks

1 ACCEPTED SOLUTION

Thanks @v-yuezhe-msft, I have since tried to re-create the query directly from the SQL databse within BI and this has worked, so I no longer have an excel file which has resolved the issue.

 

Thanks for your time on this!

 

Ryan

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

@ryan87,

How do you connect to SQL Server in Excel files? If you use Power Query option, please make sure that you load data into data model as described in this article.

In addition, when using publish to web feature, data refreshes are automatically reflected in your embedded report or visual. After refresh occurs in Power BI Service reports , it can take approximately 1 hour for refreshed data to be visible from embed codes

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Thanks for getting back to me. The datasets that we have are MS queries accessed through ODBC. I don't have a great deal of knowledge on SQL, or power queries, so this may not answer your questiion.

 

Please let me know if it doesn't.

 

Thanks

 

Ryan

@ryan87,

Do you create a ODBC data source for SQL Server database, then connect to the ODBC data source using Power Query option in Excel?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msftNo I am creating queries as per the below screenshot and then accessing the data tables from our ERP system via ODBC. I don't have power query installed on my machine, do you think this could be something to do with the issue?

 

Excel.PNG

@ryan87,

Please use the following option in Excel to connect to your data source, and follow thw guide in the "How do I make sure data is loaded to the Excel data model" part of this article to load data.
1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yuezhe-msft, I have since tried to re-create the query directly from the SQL databse within BI and this has worked, so I no longer have an excel file which has resolved the issue.

 

Thanks for your time on this!

 

Ryan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors