combining excel files from share point

[AAJahfali]

I could not combine files in power query for excel files available in SharePoint, which were exported by the Microsoft project. Even if I tried to open them by an individual without combining them then, this message would appear to me 

DataSource.Error: Web.Contents failed to get contents from 'https://seccomsa.sharepoint.com/sites/WesternRegionTIReport-M.SProjectUsers/_api/web/getfilebyserverrelativeurl('/sites/WesternRegionTIReport-M.SProjectUsers/Shared%20Documents/M.S%20Project%20Users/M.S%20Project%20Updates%20(Excel%20Type%20Only)/SPP%20ST#11.xlsx')/$value' (400): Bad Request
Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://seccomsa.sharepoint.com/sites/WesternRegionTIReport-M.SProjectUsers
    Url=https://seccomsa.sharepoint.com/sites/WesternRegionTIReport-M.SProjectUsers/_api/web/getfilebyserverrelativeurl('/sites/WesternRegionTIReport-M.SProjectUsers/Shared%20Documents/M.S%20Project%20Users/M.S%20Project%20Updates%20(Excel%20Type%20Only)/SPP%20ST#11.xlsx')/$value

also, when I tried to combine the same files from the local folder, then it will be done smoothly without any problem

if I replaced the excel files in SharePoint with other excel files that be generated by any other thing else then the power query will combine it without any problem 

sample files  put them in SharePoint then try to combine them by power query

So what is the problem and what can I do in this situation?

[AAJahfali · Accepted solution]

The Problem Solved 🎉

in the first, the files' names contained "#" 

so I deleted the "#" sign then the power query accepted to combine the files together

I feel I wanna cry

[jbwtp]

Hi @AAJahfali,

This is quite interesting.

Why are you opening them as Web.Content (Web connector). Why not using Sharepoint Folder connector? I think this is why they fail to open. Do you mind also sharing your code (at least the first couple of lines where you call the connector to fetch the data)? Maybe remove the address part for security.

Cheers,

John

[AAJahfali]

I am using SharePoint Folder 

now I tried to export from the project manager a different type "CVS" and connect it to the power query by SharePoint Folder service and still getting the same problem when I click combine the process it will not appear and when I try to open it individually then this message will come to me 

DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part).
Details:
    https://seccomsa.sharepoint.com/sites/WesternRegionTIReport-M.SProjectUsers/Shared Documents/M.S Project Users/New folder/Unit#11 Annual Inspection Updated.csv

let
    Source = SharePoint.Files("https://seccomsa.sharepoint.com/sites/WesternRegionTIReport-M.SProjectUsers/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".csv"),
    next = #"Filtered Rows"{[Name="Unit#11 Annual Inspection Updated.csv",#"Folder Path"="https://seccomsa.sharepoint.com/sites/WesternRegionTIReport-M.SProjectUsers/Shared Documents/M.S Project Users/New folder/"]}[Content]
in
    next

This is the Code