SharePoint folder data source query error

[willpage]

Hello.

I'm using SharePoint folder as a data source to hit an Excel file with Closed tickets from a helpdesk system using the following query:

Closed Incidents:

let
    Source = SharePoint.Files("https://ourcompany.sharepoint.com/sites/OurSite", [ApiVersion = 15]),
    file = Source{[Name="Closed Tickets Export.xlsx",#"Folder Path"="https://ourcompany.sharepoint.com/sites/OurSite/Shared Documents/Customer Reporting/"]}[Content],
    #"Imported Excel" = Excel.Workbook(file),
    Incident_Sheet = #"Imported Excel"{[Item="Incident",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Incident_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Type", each "Incident")

in
    #"Added Custom"

Another similar query pulls a second sheet of Service Requests from the same file, and there's a second file with the same sheet names and similar but not identical columns, with another set of queries to get those tables. The result being I have four tables, Closed tickets (Incidents and Service Requests) and Open tickets (Incidents and Service Requests).

Lastly, two more queries append two tables each and also do a whole bunch of other stuff that I won't bother posting here. The result being I have two tables for Open tickets and Closed tickets

Now, if my four "Source" queries are from local Excel files, everything is fine, I can close and apply the queries and there are no errors.

When I change the source to the SharePoint folder, it gets the data just fine, but when I close and apply I get an error like this:

It's off the screen, but the other query with errors is the Open tickets table (all 64 rows just like all 1085 for closed tickets).

The thing is, it's working perfectly. There aren't actually any error rows (the error query is an empty table). I can build a report and publish to Power BI and the scheduled refresh works. I only seem to see this in Power BI Desktop when I make a change to the queries.

It's more an annoyance than anything else, but to help me upskill with Power BI I'd really like to know why this is happening and how to fix it.

As previoulsy mentioned if the data source is local Excel there are no errors, so I don't believe the issue is with the All Tickets query, but if I need to post that I can upon request. It starts like this:

let
    Source = Table.Combine({#"Closed Incidents", #"Closed Service Request"}),
    ...

Many thanks in advance.

[Chrisinus75]

Hello,

I had the same issue. I figured it was caused by the "Content" column which is not recognized as binary type when pointing to a file located on Sharepoint.

I changed the column type as text when loading the folder, then restored the type as binary right before invoking the custom transformation function.

That stopped the issue for me.

Christian

[Greg_Deckler]

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[willpage]

Thanks @Greg_Deckler. You reckon this is a fault rather than something I'm doing wrong? If so then that's comforting (in some ways). I shall log a support ticket.

[v-lid-msft]

Hi @willpage ,

Does this issue have been resolved? How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details about it If it still not be resolved? Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[Anonymous]

Hi,

I believe I am experiencing the same error trying to read SharePoint data. This simple query:

let
    Source = SharePoint.Files("https://mycompany.sharepoint.com/sites/mysite/", [ApiVersion = 15])
in
    Source

Results in:

Clicking on "View errors" shows no error:

And there is no indication of errors in the actual query either:

The data is however loaded:

The Preview feature that was mentioned above is not available in my desktop version (2.85.985.0 64-bit (september 2020)):

This has worked like a dream up until a few days ago. If I publish the report/dataset, the refresh works without errors at powerbi.com.

I tried to do the same in PowerQuery in Excel and actually get the same behaviour. I.e. it reports errors but the data loads. 

I do not have any insights in my organization's SharePoint setup, but could this be a problem with it?

Any help on this issue would be very appreciated!

Thanks,

/Lars

[willpage]

Hi @v-lid-msft. Thanks for the following up. I have not yet had a chance to try these suggestions. As it's working, the error is more an annoyance, so it's at the bottom of my to-do list.

[v-lid-msft]

Hi @willpage ,

As well We did not found any obviously error in the queries you provided, Does this error also happened in the previous version of power bi desktop? Could you please try to ues "View" - "Column quantity" to find if there are any column contain error? What is the error when you apply queries (Click view error). Does the " Store datasets using enhanced metadata format" feature enabled in "options" - "preview feature" (currently, if enabled, there may some issue when apply queries, such as tmsavepointvalidation error)

Best regards,