SOLVED

Power Query missing "from SharePoint folder" option

New Contributor

Hey All, 
I am trying to connect to a sharepoint folder, However the option is just missing, I keep seeing guide that tell me to use the option but I don't have it. 

I have a microsoft office 365 business premium account 

 

Thanks in advance. 2.png

9 Replies
best response confirmed by MichaelYe (New Contributor)
Thank you Sergei
Hello Sergei. What about Office 2013? How I can create SharePoint folder connection with Excel? Thank you!

@Timo1950 

You may try to install Power Query from here Download Microsoft Power Query for Excel from Official Microsoft Download Center but for SharePoint connector you have to have Pro version of Office.

Please note

 

@Timo1950 

Once you have the Power Query Add-in installed...
Create a new blank (Power) query > Advanced Editor > Select all > Paste this code:
(gives you a table of all PQ M functions available in the in used product - the PQ Add-in in your case)

 

let
    // #shared library excluding this workbook's Queries & custom functions
    Source = Record.ToTable(
        Record.RemoveFields(#shared,
            Record.FieldNames(#sections[Section1])
        )
    ),
    SelectedTypeFunction = Table.SelectRows(Source, each [Value] is function),
    RemovedValue = Table.SelectColumns(SelectedTypeFunction, {"Name"}),
    SortedByName = Table.Sort(RemovedValue, {{"Name", Order.Ascending}}),
    RenamedColumn = Table.RenameColumns(SortedByName,
        {{"Name", "Function name"}}
    )
in
    RenamedColumn

 

Check the list but top of my head functions SharePoint.Contents, SharePoint.Files & SharePoint.Tables should be there. AFAIK there's no restriction to access a Sharepoint site as long as you have the appropriate credentials, in which case:

  • Create a blank query
  • In the PQ Editor formula bar enter:

 

=SharePoint.Files("<TheRootAddressOfTheSharepointSite>", [ApiVersion=xx])

 

(for xx, check the function doc.)

  • Validate (you'll be asked for your Credentials) and you should get what you need

 

See i.e. Connecting to Files in SharePoint & OneDrive with Power BI / Power Query that discusses the diff. between SharePoint.Files & SharePoint.Contents

@L z. 

As a comment, I'm not sure add-in for 2013 supports ApiVersion mentioned in documentation (I guess 15). Better to skip or use null. Or use one which appears by default for this version of PQ.

I couldn't/can't check that aspect so thought an example + hlink to doc. could help @Timo1950
Nevertheless trying without the option first is a good recommendation. Thanks

@L z. 

ApiVersion 14 was buggy, #15 is also not free of bugs but it is updated from time to time. In any case that's not a good idea to use PQ last updated couple couple of years ago, but perhaps for the concrete task it works.