Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Missing Functionality from Power Query - any ideas?

  1. #1
    Seeker Hypnopoison's Avatar
    Join Date
    Oct 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Missing Functionality from Power Query - any ideas?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,245
    Articles
    0
    Excel Version
    Office 365 Subscription
    I may be wrong, but I think that functionality may only be available in a 365 subscription or Excel 2019 installation.

    https://powerbi.tips/2016/09/loading...om-sharepoint/
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker Hypnopoison's Avatar
    Join Date
    Oct 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Hey Ali, thanks for replying so quickly. That was my fear :-( Thanks

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,337
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker Hypnopoison's Avatar
    Join Date
    Oct 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Hi Ken, oh yes please :-)

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,337
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Seeker Hypnopoison's Avatar
    Join Date
    Oct 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

    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

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,337
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Seeker Hypnopoison's Avatar
    Join Date
    Oct 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Query

    Quote Originally Posted by Ken Puls View Post
    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.
    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.

  10. #10
    Seeker Hypnopoison's Avatar
    Join Date
    Oct 2016
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Actually, I suspect that our SharePoint is on premise, if that has any impact I don't know.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •