Clarification on Power BI Data Connections

[i2rsantos]

Hi There,

I have questions about what happens behind the scenes when I import an Excel spreadsheet into Power BI Desktop from a file on my local computer.  When I do this, does this mean there is a 'live connection' to the underlying Excel spreadsheet?  So, whatever I do will be reflected in the  .pbix referencing that spreadsheet if I edit the spreadsheet.  Is the connection broken when I publish to the Power BI service?

On a secondary note, how do I reference an Excel spreadsheet in my OneDrive for Business?  When I choose Excel as a data source, Power BI Desktop takes me to my local file system.

[v-xiaosun-msft · Accepted solution]

Hi @i2rsantos ,

There are three connection modes in Power BI: Import, Direct Query, and Live Connection.

When the report runs in Import mode, the data is queried from the data source and stored in the cache on a schedule.

When a report operates in DirectQuery/Live mode, data is queried live from the data source. There is no caching involved.

Thus, when you import an Excel spreadsheet into Power BI Desktop, it does not mean there is a 'live connection' to the underlying Excel spreadsheet. After you do some changes in your excel, you need to right click the table and then click "refresh data", you will see the table upgraded.

If you publish the report to service, since the data source is a local file, so you need to use a gateway to connect to the data source. With a gateway, you can configure a scheduled refresh.

If you want to reference an Excel spreadsheet in your OneDrive for Business, you need to use "Get Data" ->"Web".

Then paste the link of your excel to it.

Please reference to the following documents.

Power BI connection modes – Davidson Technology & Innovation

Get data from Excel workbook files - Power BI | Microsoft Learn

On-premises data gateway - Power BI | Microsoft Learn

Add or remove a gateway data source - Power BI | Microsoft Learn

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.