Downloading a Sharepoint file into Power BI

[ptencza]

I am quite new to Power BI and am the only one familiar with it at all at work; I am trying to connect a sharepoint folder to BI so I can create a report and just have it automatically refresh each month once I update the data within Sharepoint. I always go to Get data - More - Sharepoint folder - Upload Root URL - and hit load data. I end up getting an error message. 

I believe the data isn't pulling in because it is only allowing me to link to my root URL; I have seen videos where people are able to login to their company's sharepoint it and access it that way. I just put in my root URL for sharepoint and it tries to pull data directly from it as opposed to letting me clarify which folder has the excel file I need it to pull information from. 

Everyone who has tried to help shows me old links from 2016 to 2019 that don't work anymore. Please let me know if you have any advice on how to load my sharepoint or is able to show a tutorial of uploading it I would greatly appreciate it. I am trying to do this for work and no one else at my company knows BI

I posted the error that pops up below.

"Load

Query2

This query does not have any columns with the supported data types. It will be disabled from being loaded to the model."

[ptencza]

@AlexisOlsonThank you for that! After following your instructions Power Query just took a very long time to load and never actually loaded. It accepted the query, and seemed like it was accessing Sharepoint, but it just continued to load for about 10 minutes without anything happening. It said loading "documents" and that it is waiting for the sharepoint to connect, but it will not finish loading so nothing happens

[AlexisOlson]

That's unfortunate but I can't tell what the issue is at this point so I don't know what to advise now.

[ptencza]

Thank you for that! I ran the Query and it seemed like it was going to work but it has just had the loading symbol for the past 10 minutes in my Power Query. I closed BI and tried it again and the same thing happened. It didn't detect any errors, but it is taking an exceptionally long time to try to run the query. What do I do if it simply won't stop loading and nothing happens?

[AlexisOlson]

Does this 2020 video from Curbal help?

https://www.youtube.com/watch?v=3GIz50pftZ0

[ptencza]

@AlexisOlson It helped a bit but now fully - possibly I am doing something wrong? The file I need is within a few folders since it is the Sharepoint for my entire company. When I enter the root URL it shows the Documents folder that has the other folder I need to access within it, but it just asks if I would like to load the data within my documents folder, which doesn't exactly make sense to me. We have numerous clients and you have to go through a few various folders to get to their information, I am not sure how to tell BI that it needs to go through different folders in order to access my data since it only allows you to enter the root URL and I can't choose folders to look through

[AlexisOlson]

I'm not sure I understand where you're running into issues. When I connect to a SharePoint site it shows all the files on the site regardless of what subfolder they live in but I can still select a folder by filtering the Folder Path column.

Suppose I want to combine all of the Excel documents in the folder

https://company.sharepoint.com/sites/SiteName/Financials/2021/Q4/

I could get the set of these files as follows:

let
    Source = SharePoint.Files("https://company.sharepoint.com/sites/SiteName/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each
        [Folder Path] = "https://company.sharepoint.com/sites/SiteName/Financials/2021/Q4/"
        and [Extension] = ".xlsx")
in
    #"Filtered Rows"

From there, do the file combination.

If you need to connect to multiple SharePoint sites, then you'll need multiple connections (multiple queries or multiple lines in a single query using SharePoint.Files).

[ptencza]

I am most likely explaining or doing something wrong. Before I make it into Power Query when I search my Root URL it shows a few folders that subfolders are within. If it showed me all the subfolders withing the URL I give then I should be seeing hundreds of folders instead of 3. This time I chose to disregard that and try to enter the query you listed into power query starting with 

Source = SharePoint.Files("https://company.sharepoint.com/sites/SiteName/", [ApiVersion = 15]),

(I copied and Pasted this query and simply swapped out my root URL since I am not too familiar with queries) and I got this error before moving on to the next part of the query:

Expression.SyntaxError: Token Eof expected.

[AlexisOlson]

Show me your whole query. It sounds like you're missing parenthesis or comma or something.

I'd recommend starting from this as your entire query.

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

[ptencza]

I am sure I am pretty far off, but I am not sure what else I need to change

[AlexisOlson]

This does clarify what's going on somewhat. It appears to be interpreting your M query as a string. Try pasting what I suggested into the Advanced Editor, not the formula bar.

(Most times you see "let ... in ..." in a community answer, it's an entire query rather than a single step.)

The other thing that looks odd to me is that your URL has "/2.0/2.1/" instead of "/sites/SiteName/" as I'd expect. This might be OK but it isn't a URL format I'm familiar with.