SharePoint Folder Issues -> SharePoint Refresh Issues (comprehensive thread)

[Anonymous]

Hello,

This is going to be a rather lengthy question and a lengthy topic because, quite frankly, the problem seems to be so convoluted at many different levels all involving SharePoint Connection and Integration with PowerBI.  I spent the entire workday yesterday looking through about every SharePoint thread on this forum and none of them seemed to directly address my issues, nor did the threads/solutions ever offer clear and concise fixes.  I am going to to try to be as explicit as possible in outlining my goals, the issues, and the attempted fixes I have tried. Please excuse if anything seems redundant.

The goal:

Connect PowerBI Desktop file with both files located in an organizational SharePoint and lists located in an organizational SharePoint.

Pretty straightforward.

The issues:

Issue #1: SharePoint UI connection (I'll explain why I put UI later) only works for List connection and ONLY with the SharePoint URL starting with http:// as opposed to https:// . I am getting the dreaded "Access to the Resource is Forbidden" error. See following Pictures Below:

Why is this an issue? The issue is that when I try to refresh/schedule refresh this particular dataset in PowerBI Service I get a wrong credentials error.  Everything I have read so far says that one of the ways to fix this is to change the URL from HTTP to HTTPS. Unfortunately, I am unable to do this because of the above error and therefore the dataset is unable to refresh in the PowerBI Service.

Issue #2: SharePoint Folder Connection does not work at all

As I've stated above, I have no issue connecting to SharePoint online list but ONLY when I use HTTP.  However, when I try to connect to a SharePoint Folder using either HTTPS or HTTP I get the following errors:

For http://site.sharepoint.com:

 

For https://site.sharepoint.com:

Workaround For Issue #2:

Surprisingly, the following workaround appears to work for Issue #2, though it is  extremely cumbersome.

Create a Custom Blank Query and use PowerQuery Code to navigate to the sharepoint folder/file directly.  Again - glad this works - but not sure this is what uses should resort to as PowerBI is supposed to offer seamless SharePoint Connectors:

let
    Source = SharePoint.Contents("http://sitename.sharepoint.com", [ApiVersion = 15]),
    #"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
    #"Folder Name" = #"Shared Documents"{[Name="Folder Name"]}[Content],
    DBA = #"FolderName"{[Name="DBA"]}[Content],
    #"PowerBI Files" = DBA{[Name="PowerBI Files"]}[Content],
    #"File Name csv" = #"PowerBI Files"{[Name="FileName.csv"]}[Content]

Here, I am using the SharePoint.Contents method of PowerQuery to navigate to the organizational SharePoint site. I guess I am confused on how this works - but none of the other ways to navigate to sharepoint are working. It appears that the SharePoint Folder method is not working because it is using the Web.Contents method of PowerQuery as it's underlying call instead of the SharePoint.Contents method. Am I right in that assumption? Again, this call in PowerQuery only works with HTTP and not HTTPS.

Issue #3: SharePoint credentials showing up as invalid in Service Refresh.

• Currently, all my files that pull into my dataset (outside of two sharepoint lists) reside locally on my computer. Because of this, I use a datagateway to refresh the dataset into the service.  It works correctly except for SharePoint. It is giving me the "invalid credentials" error in the service.
• I have changed, reentered, disconnected the SharePoint Connection and currently have it setup with the following Credentials: Organizational Account, Privacy level: Organizational

Here is what options I have for credentials in the PowerBI Service:

I suppose my questions come down to the following:

1. Why can't I connect to the HTTPS version of SharePoint through PowerBI Desktop, and is that failure to connect to the HTTPS version causing me to not be able to refresh dataset in the Service?
2. Why can't I connect to any other SharePoint data connecters through PowerBI Desktop when the SharePoint List works fine.
3. Why does the sharepoint Folder method use Web.Contents instead of SharePoint.Contents in underlying PowerQuery call?
4. What is the proper methodology for utilizing SharePoint in datasets published into the Service? Should it only be sharepoint? Can you have mix of SharePoint files and lists? Can you have SharePoint files/lists in the same dataset as local files?

This has not been as straightforward as it should be, with tons of different errors - and I am just trying to figure out why and can't seem to get a straight answer from any previous threads. Apologies for the slight Service overlap here in Desktop Forum but the reality is most of my issues are stemming from Desktop.

Michael

[Greg_Deckler]

First question, what version of SharePoint? Is it on-premises or 365?

[Anonymous]

@Greg_Deckler We are using 365 SharePoint. We have MFA turned on for all accounts through AAD.

[Greg_Deckler]

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18527482-mfa-authentication

[Anonymous]

@Greg_Deckler 

So, I have come across this support issue before - but I'm a little confused as to how it is effecting my question. Is that the reason why I can't connect using HTTPS? Is that the reason why I can't connect to SharePoint files using the Get Data methods and only through PowerQuery coding?  Is this the reason why it won't update through SharePoint into the PowerBI Service?

Admittedly, I am not an expert in this so I'm struggling to connect the dots. 

[Greg_Deckler]

I would say that all of those are a pretty safe bet. You could confirm 100% by turning off MFA for the account being used or using a different account without MFA but there a a number of threads in the forums where people had their SharePoint resources working just fine and then started using MFA and it broke everything. Here is one such thread:

https://community.powerbi.com/t5/Issues/OAuth-and-SharePoint-Online-Failed-to-update-data-source/idi...

[Anonymous]

After double checking: I do not have 2FA/MFA turned on for my account.

Michael