Share via

How to Refresh power query in webExcel stored in SharePoint in the background

Anonymous
2023-01-30T19:07:59+00:00

What I need help achieving is:

The data in the Excel file stored in Sharepoint is refreshed

(either live, scheduled very hour, or triggered via Power Automate) without opening the file

I also need this Excel data to be synced live/scheduled to a Microsoft List

Background information:

  • I have an Excel file stored in SharePoint,
  • the excel file extracts data from a PBI Dataflow via PowerQuery and stores in Excel file in the form of the table
  • I am able to 'Refresh All" data on Excel Desktop on demand., however unable to refresh on Sharepoint. If attempting to refresh ondemand on web I receive the message "COULDN’T REFRESH DATA This workbook contains Power Query queries using data sources that can't be refreshed in Excel for the Web."
  • I am only aware of creating a 'Microsoft List' from an excel stored on a personal OneDrive and not a Sharepoint location

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-01-31T07:05:37+00:00

    Dear be_better_grow

    Good day! Thank you for posting in Microsoft Community. We are happy to help you.

    Based on your description, I noticed you have two concerns based on your description, please check the below information, we'll answer then one by one for you.

    Q1: Refresh the Excel data stored in SharePoint without opening the file.

    A1: Since you mentioned that the data you extracted from a PBI Dataflow via PowerQuery, per pour test and search, you can use power automate flow to achieve this requirement. Please understand that this query of flow is outside of our support boundaries. So please kindly understand that as engineers in here have limited experience in Power Automate , we don't have further resource and permissions to do the further test and investigate to offer further support in a more efficient way. For such a developed scenario, we really hope you can get dedicated assistance to further investigate. Kindly post a thread in Microsoft Power Automate Community - Power Platform Community the supports engineers there have the correct escalation channel and they are specialized in this kind of questions. They have more experienced and professional with this query connected with other server environments. I am sure that our experts from that team can address your query effectively and accurately.

    Q2: Sync the Excel data to a Microsoft List

    Generally, **** if we created a list based on Excel data which stored in SharePoint, you can also use Microsoft Power Automate to help you achieve your requirement. You mentioned that creating a 'Microsoft List' from an excel stored on a personal OneDrive. You can upload the Excel file to your work account onedrive, then use the automate to achieve this meet.

    For your reference: Power Automate: Sync Excel to a SharePoint list - Manuel T. Gomes (manueltgomes.com) Solved: Two-Way-Sync between SharePoint and Excel - Power Platform Community (microsoft.com)

    If you want to learn more about it, to make sure you get professional help, we’d you go to  Power Automate Community to post a new thread. Which is specific channel to handle related questions. And engineers in that forum will focus on your specific scenario and provide specific suggestions.

    Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

    Sincerely,

    Stacey | Microsoft Community Moderator

    0 comments