Page 1 of 6 123 ... LastLast
Results 1 to 10 of 56

Thread: Power Query and SharePoint
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query and SharePoint

    Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product.

    So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In.

    My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"?

    I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively.

    Thank you.

    Cliff

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    "store my files" -- are you talking about the final xlsx file with the powerpivot model and reports? However you can get that up on sharepoint is peachy. Doesn't matter.

    If you are talking about the source files (that PQ is pulling from), ... then it kinda matters, but only if you are worried about auto-refresh scenarios.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    Sorry, that was kind of vague, wasn't it?

    I meant the source files - where should they be housed?
    Last edited by cmcreynolds; Jul 1st, 2015 at 09:08 AM.

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    You can create a Mapped drive on your PC which is essentially a copy of a SharePoint document library. You can then point power query to the files on your PC and operate as normal using power query

    Using SharePoint Document Library as a Network Drive | Mr. Office 365

  5. #5
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    if you're using SharePoint 2013 or SharePoint online then you can use this:
    https://support.office.com/en-sg/art...rs=en-SG&ad=SG

    and simply query the files from your SharePoint folder (and reading the binaries) instead of downloading the files locally.

    Note that the speed and performance may vary depending on your SharePoint server.

  6. #6
    Board Regular TimRodman's Avatar
    Join Date
    Jan 2015
    Location
    Columbus, OH
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    Also, you might want to take a look at Power Update if you want to automatically refresh your PQ/PP solution.



  7. #7
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    Thank you very much -

    First, TimRodman - I have set each individual connection to refresh whenever the file is opened (online or on the desktop) - and it seems to work without an additional app.

    MattAllington - I do have my Sharepoint lists mapped on my PC, I just didn't know if that mapping would work (efficiently?) with Excel Online.

    Miguel.escobar - do I have to utilize the SharePoint.Contents if it's mapped to my PC.


    So, it sounds to me like just having it mapped to my PC works best (and easiest in PQ, from what I saw). However, when a coworker shares a SharePoint site with me and I have edit permission, will that show up where Sharepoint is mapped?

    Like I said in my original post, I just want to use both products most efficiently. Also, I'd be totally cool with having both source files and reports on Sharepoint - does that matter in this conversation?

    Cliff
    Last edited by cmcreynolds; Jul 6th, 2015 at 02:18 PM.

  8. #8
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    you don't need anything in your PC if you use SharePoint.Contents. The good thing about this is that you can share this query with other colleagues and that'll work for everyone whereas the local file path would be something that might only work for you

  9. #9
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    Miguel - thank you. Would I be able to read the entire "site" like a folder in PQ? I plan on adding a lot of files to the site each week and would rather not have to set up a separate query for each file added.

    Is that how SharePoint.Contents works?

    Do I access SharePoint.Contents through PQ's "From Other Sources>From SharePoint Lists"? I thought the list was different than the documents. When I use the "From SharePoint Lists" I do not see my workbooks, just

    ComposedLooks
    Microfeed
    UserInformationList
    Last edited by cmcreynolds; Jul 6th, 2015 at 05:05 PM. Reason: Further Questions

  10. #10
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query and SharePoint

    This is how it looks like:


    It is pretty similar to the "From Folder" experience

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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