Forum Discussion

SAI-Indy's avatar
SAI-Indy
Copper Contributor
Apr 25, 2019

Excel Power Query Refresh fails for a shared file owner on SharePoint using Office 365 Business Prem

I have recently generated several Excel spreadsheets on our office 365 sharepoint drive.  I use Power Query in Excel to manipulate data and generate Pivot reports in the excel spreadsheets.  All refe...
BI & Data Analytics
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 25, 2019

SAI-Indy , how exactly do you reference files on SPO? Correct query is like

 

Sharepoint.Files(<root site URL>) 
Filtrer file by name 
Get binary content 
Get object from file (table or sheet)

  • SAI-Indy's avatar
    SAI-Indy
    Copper Contributor
    Apr 25, 2019

    SergeiBaklan 

     

    Here' the general flow:

    1a) SharePoint.Files("https://mydomain.sharepoint.com/sites/Commissions/", [ApiVersion = "Auto"]))

    or

    1b) SharePoint.Files("https://mydomain.sharepoint.com/sites/Commissions/myfilename.xlsx")

    2) Filter files by name (for option 1a above)

    3) GetExcelObjects (filter by kind=sheets)

    4) Expand tables

    5) ...manipulate data

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 26, 2019

      SAI-Indy , never seen any problems with 1a

       

      IMHO, 1b shall not work

      • martingarner's avatar
        martingarner
        Copper Contributor
        Jul 30, 2019

        SergeiBaklan 

        Is there a generalised step-by-step guide for how to share power query enabled Excel files, without hitting this error, please. I have the same error (with two files and do not understand a) why it's there or b) why some users can refresh, while others cannot or c) what to do to fix it. Any help gratefully received, please.

        Thanks,

Resources