Power BI - Connecting to a Live Excel Doc Data Source

[niyati_61]

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!

[d_gosbell · Accepted 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.