Power Query to SharePoint folder

[melimob]

Hi 

Objectives:

Excel document

Read from Sharepoint/one drive folder csv files

Real time connection (any time the file is dropped, it will refresh, or at least refresh upon opening)

Other users can open the excel and see the latest info/refresh, not just the creator

So far, I've only found a way to connect to SP/one driver if I use Power BI desktop but then I have to republish to power bi app for the data to then be refreshed in excel as I am using that dataset.

Can anyone please advise if I can take Power BI out of the equation, read from SP folder and have it automatically update all in 'Power Query/Get and Transform' instead?

thanks

[dax · Accepted solution]

Hi @melimob , 

As I know, when you use "live connection", you  should get the newest data when you set schedule or use "refresh now". If you use sharepoint online, Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization. Or you also could set  schedule or use "refresh now". You could refer to refresh-data for details. I think the users could view the newest data when they open report or click refresh in report page.

Best Regards,
Zoe Zhi

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

[edhans · Accepted solution]

@melimob the only way to get it to refresh automatically when a new file is dropped is to create a Flow using Power Automate that will monitor that folder for new/updated files, then trigger a data refresh. However, normal workspaces can only be refreshed this way 8 times a day, and Premium workspaces 48 times a day. Any refreshes triggered beyond that will not happen. You can manually refresh as many times as you like though. 

For more detailed assistance, I recommend you ask in the Power Automate forum on how the Sharepoint and Power BI connnectors work, and how to build your flow.

[Anonymous · Accepted solution]

Hi @melimob

Well. There is a few options depending on the characteristics of your dataset and query.

1. Using PBI as a medium. As described in @edhans post - use Power Automate to trigger refresh on source file update. Sync data to Excel either via refresh or permanent connection using Analyse in Excel to create odc connection file. If the end-point is pivot table you can set Refresh when opening flag and the data will refresh each time the file is open. It is also possible to refresh on schedule, but I did not test it. Connecting via odc connection file is a permanent connection and therefore (with small caveats) always read most recent data from a connected PBI.

2. If your query is quite fast you can run it directly from Excel using Power Query. It perfectly supports reading data from Sharepoint. This also can be configured to refresh each time when the file is open (tested) and on schedule (I did not test it). I guess by the sound of your question this is what you want, but unlike #1 this can not initiate refresh instantly when source data is updated.

3. If the query is quite long to run you can use PBI functionality to refresh data in connected Excel files. There are few caveats and tricks there - e.g. I was using Automation to swap files where refresh resulted in large files, so I found #1 is more preferable. However, if you are absolutely keen to take PBI out of equation it is good to know there is another option.

Kind regards,
JB

[edhans · Accepted solution]

@melimob the only way to get it to refresh automatically when a new file is dropped is to create a Flow using Power Automate that will monitor that folder for new/updated files, then trigger a data refresh. However, normal workspaces can only be refreshed this way 8 times a day, and Premium workspaces 48 times a day. Any refreshes triggered beyond that will not happen. You can manually refresh as many times as you like though. 

For more detailed assistance, I recommend you ask in the Power Automate forum on how the Sharepoint and Power BI connnectors work, and how to build your flow.

[dax · Accepted solution]

Hi @melimob , 

As I know, when you use "live connection", you  should get the newest data when you set schedule or use "refresh now". If you use sharepoint online, Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization. Or you also could set  schedule or use "refresh now". You could refer to refresh-data for details. I think the users could view the newest data when they open report or click refresh in report page.

Best Regards,
Zoe Zhi

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