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

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
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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!