Missing Functionality from Power Query - any ideas?

H

Hypnopoison

New member
Joined
Oct 29, 2016
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
Hi guys, I was hoping you could help me figure out why I am missing a really valuable Power Query feature. I have Power Query on my new laptop, which is running Office 2016 (an MSI installation - don't ask, its a work restriction). But when I look at the Power Query data connection options I am missing the option to connect to a Sharepoint Folder. Everything else seems to be there though.

Have you encountered this before and do you know how I can solve it. Do you think my version of Excel just needs updating?
 
Hey Ali, thanks for replying so quickly. That was my fear :-( Thanks
 
Hi Hypnopoison,

So the good news is that the connector works, but the bad news is that it's not exposed in the User Interface in 2016. :(

Do you need the code for the connector? You can do it via a blank query...
 
Raw M code would look like this:

Code: 
let    Source = SharePoint.Files("https://xxxx.com/sites/yyyy/", [ApiVersion = 15])
in
    Source

So if I was on Office 365 with a tenant of datamonkey, connecting to the rockets site, it would look like this:

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

If I'm not using Office365, but rather a "local" SharePoint install, it would be:
Code: 
let
    Source = SharePoint.Files("https://datamonkey.com/sites/rockets/", [ApiVersion = 15])
in
    Source

Sometimes you also have to drop this part to make it work:
Code: 
, [ApiVersion = 15]

Hope that helps!
 
Errors

Hi Ken, you are a star!
I must be doing something wrong though, as I am getting the following errors:

* With the API Version code:
Expression.Error: 2 arguments were passed to a function which expects 1.
Details:
Pattern=
Arguments=List

* Without the API Version code:
Expression.SyntaxError: Token RightParen expected.

Any help to decipher what these errors are telling me is much appreciated.
Regards
Debbie
 
Can you post the M code you're using? To do this:

  • Go into the query, then go to View --> Advanced editor.
  • Copy everything in there and paste it into a window here
  • Change your SharePoint domain to xxxx.com like I did

That way I can see the exact code that is giving the error. It could be as simple as a missing punctuation mark, or more complicated.
 
Query

Ken Puls said:
Can you post the M code you're using? To do this:

  • Go into the query, then go to View --> Advanced editor.
  • Copy everything in there and paste it into a window here
  • Change your SharePoint domain to xxxx.com like I did

That way I can see the exact code that is giving the error. It could be as simple as a missing punctuation mark, or more complicated.
Click to expand...

Thanks Ken, really appreciate your help :)
I'm wondering if it has something to do with the naming my org has for its SharePoint sites. Its not a .com just a name. I've tried removing the %20 and different variations of the below location and nothing seems to work. I've also added in Sharepoint.com in various places to see if that works :-( I'm stumped. I believe it is a local installation, and not Office 365.

The code I am using is:

Code: 
let
    Source = SharePoint.Files("http://xxxxxx/Department/Sales/Shared%20Documents", [ApiVersion = 15])
in
    Source

Thanks.
 
Actually, I suspect that our SharePoint is on premise, if that has any impact I don't know.
 
The on-prem could impact it, depending on how current the install is. Usually I'd knock of the api bit to solve that problem.

The issue is that you're trying to go one level too deep, hitting the Shared Documents folder. This should work for you:

Code: 
let    Source = SharePoint.Files("http://xxxxxx/Department/Sales", [ApiVersion = 15])
in
    Source

If not, try this:

Code: 
[FONT=Verdana]let[/FONT]
    Source = SharePoint.Files("http://xxxxxx/Department/Sales")
in
    Source

From there you need to filter down into the required folders, but you always have to connect to the root folder to begin with SharePoint. (Frustrating design decision there!)
 
You must log in or register to reply here.
Back
Top