Forum Discussion

Minsen's avatar
Minsen
Frequent Visitor
5 years ago
Solved

Excel on Sharepoint Data Source Creds Error

Hi there   I have an Excel Workbook sitting on Sharepoint which I connect using:   =Excel.Workbook(Web.Contents("https Link to *.xlsx File on Sharepoint"), null, true)   In Desktop, I can succe...
Need Help
  • Minsen's avatar
    Minsen
    5 years ago

    Hi GilbertQ,

     

    I have found a solution by tweaking the link to the Excel file on Sharepoint as follows:

     

    From https://[companyname].sharepoint.com/:x:/r/sites/[RestOfLink]

    To https://[companyname].sharepoint.com/sites/[RestOfLink]

     

    Essentially just leaving out /:x/r. With this type of link + =Excel.Workboook(WebContents()) I have the option to use "OAuth2" without gateway and (!) it works with my account which has both MFA & PW changes over time (for the latter I will need to re-sign in after every change, of course).

     

    I scheduled an automatic refresh for last night and it worked like a charm.

     

    Thank you so much once more for your help.

     

    -m

GilbertQ's avatar
GilbertQ
Super User
5 years ago

Hi Minsen 


Can you make sure that you have got the latest version of PBI Desktop and the PBI Gateway installed.

 

Further more I do think you might have it tough with MFA on your account to be used for authentication. I would suggest getting a user account that does not have MFA or the password being changed over time.


This same account would then be added to the SharePoint group.

Minsen's avatar
Minsen
Frequent Visitor
5 years ago

Hi GilbertQ 

Thank you for your help. I have updated PBI Desktop and the gateway software. However, I was not aware I needed an on-premise gateway to connect in this case? On PBI Service under Dataset Settings -> Gateway Connection it reads:

You don't need a gateway for this dataset, because all of its data sources are in the cloud, but you can use a gateway for enhanced control over how you connect.

 

I can use the Gateway though and add the Web data source, but then again I cannot seem to connect to it for invalid creds. I can select from "Anonymous", "Basic", "Windows" & "OAuth2".

 

Will try to get an account w/o MFA and password changes over time, but don't think IT guidlines will allow for that. Is this a must to solve the issue?

 

-m

  • GilbertQ's avatar
    GilbertQ
    Super User
    5 years ago

    Hi Minsen 

     

    I would suggest trying that first with the account without MFA and see if that works?

    • Minsen's avatar
      Minsen
      Frequent Visitor
      5 years ago

      Hi GilbertQ,

       

      I have found a solution by tweaking the link to the Excel file on Sharepoint as follows:

       

      From https://[companyname].sharepoint.com/:x:/r/sites/[RestOfLink]

      To https://[companyname].sharepoint.com/sites/[RestOfLink]

       

      Essentially just leaving out /:x/r. With this type of link + =Excel.Workboook(WebContents()) I have the option to use "OAuth2" without gateway and (!) it works with my account which has both MFA & PW changes over time (for the latter I will need to re-sign in after every change, of course).

       

      I scheduled an automatic refresh for last night and it worked like a charm.

       

      Thank you so much once more for your help.

       

      -m

      • GilbertQ's avatar
        GilbertQ
        Super User
        5 years ago

        Thanks for letting us know and I have to say I thought that you had got the link via the file in Excel and not via the browser


        Glad to hear you got it working!