Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello @d_gosbell,
Currently I have this Live Excel Document - it refreshes the data when you click on the Refresh button, it directly takes the data from the company SQL database. And the the data gets refreshed in this Excel Doc.
After connecting this Excel Document through Get Data - Excel in Power BI. Now, when I click on the Refresh button in the Excel doc and it refreshes the data; I click on Refresh Preview in Power BI too, but it did not refresh the data in PBI?
How do you connect a live Excel sheet like that in Power BI so that the data refreshes when the excel refreshed too?
Also, how does this refreshing work in Power BI Desktop for report server?
Thanks in advance!
Solved! Go to Solution.
@niyati_61 wrote:
How do you connect a live Excel sheet like that in Power BI so that the data refreshes when the excel refreshed too?
It's actually better to not do this at all. The "Get Data" in Excel is using the same Power Query engine that Power BI uses (although a much older version). So the best approach is to edit the query in Excel, go into the advanced view, then copy all the text. Then go into Power BI, create a new blank query, go into advanced view and replace the text that is in there with the text you copied from Excel. Then Power BI will be connected directly to SQL and you can cut out the need for the Excel file in the middle.
One of the problems with your current approach is that the Power BI Excel connector reads the contents of the Excel file off disk. So if you click refresh, but do not save the file then Power BI will read an old copy of the data. And Power BI just reads the contents of the Excel file. It does not know there is another query behind the data and it has no way of triggering the refresh button.
@niyati_61 wrote:
How do you connect a live Excel sheet like that in Power BI so that the data refreshes when the excel refreshed too?
It's actually better to not do this at all. The "Get Data" in Excel is using the same Power Query engine that Power BI uses (although a much older version). So the best approach is to edit the query in Excel, go into the advanced view, then copy all the text. Then go into Power BI, create a new blank query, go into advanced view and replace the text that is in there with the text you copied from Excel. Then Power BI will be connected directly to SQL and you can cut out the need for the Excel file in the middle.
One of the problems with your current approach is that the Power BI Excel connector reads the contents of the Excel file off disk. So if you click refresh, but do not save the file then Power BI will read an old copy of the data. And Power BI just reads the contents of the Excel file. It does not know there is another query behind the data and it has no way of triggering the refresh button.