cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
willpage
Helper II
Helper II

SharePoint folder data source query error

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:

image.png

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.

7 REPLIES 7
Chrisinus75
Frequent Visitor

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
Super User
Super User

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".


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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.

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

larssundberg_0-1602322246485.png

Clicking on "View errors" shows no error:

larssundberg_2-1602322617877.png

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

larssundberg_3-1602322849045.png

The data is however loaded:

larssundberg_1-1602322515942.png

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

larssundberg_4-1602323093538.png

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

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.

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,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors