locked
Power Pivot 2013 Using SharePoint 2013 List as Source Data RRS feed

  • Question

  • Background: On a SharePoint 2013 site, there are lists that have the exact same columns but due to their size I had to create a different list for each year. These are essentially my data lists or tables that end users enter data into. These lists have look up columns in them, referencing columns from look up lists or tables located on the same site.

     

    Goals: 1) Create a Power Pivot Gallery on SharePoint 2013 that is able to leverage the "manage data refresh" feature using the SharePoint 2013 List data that is on the same SharePoint 2013 site.   2) Combine all the data lists/tables into one table once in Power Pivot 2013. The Look Up lists/tables will remain separate.

     

    Challenge: Being able to merge or do a union query to the "data lists" and still maintain the ability to leverage the "manage data refresh" feature.  This is a tactical, quick fix solution, and I cannot use SQL Server and am operating under the assumption that I do not have Access Services 2013 as an option.  

     

    Failed Attempt #1: What I already tried was linking the lists to an Access 2013 database and did a Union query from Power Pivot which merged the data from the different data tables as I wanted it to. Then I uploaded that Access Database to a document library on that same site, thinking that I could maintain the ability to leverage the "manage data refresh" feature but I was wrong.


    Any thoughts or suggestions?  Thanks in advance for any help!

    Cheers, Johnny



    Johnny

    Monday, December 29, 2014 5:48 PM

Answers

All replies

  • You can connect to SharePoint Lists using Power Query (and of course Union multiple Lists and more using Pwer Query) - but Automatic refresh would not work with SharePoint. Power BI (Cloud service) allows auto refresh of Power Query, but not on SharePoint. Hopefully some day soon.

    Regards, Avi www.powerpivotpro.com PowerPivotPro

    Wiki:How to ask a Power Pivot Question to get a prompt, accurate and helpful response

    Monday, December 29, 2014 6:28 PM
  • Thanks for the guidance, I hope so too.

    I wonder if you can connect Power Pivot 2013 to a "Linked Data Source" on a Web Part page using SharePoint Designer as described in this article?(Note I would try but don't have access yet).



    Johnny

    Monday, December 29, 2014 11:09 PM
  • You should be able to build something similar, but remember there are lot of constraints working with Lookup columns in defining such views.

    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Monday, December 29, 2014 11:45 PM
  • Thanks Ashish for your reply. Could you be more specific when you say that I could build something similar? I appreciate your help!


    Johnny

    Tuesday, December 30, 2014 12:56 AM
  • The example which you posted for consolidated data view, you should be able to build that.

    Now it depends whether the lookup columns can support the view building or not. As I know lookup columns / metadata based columns does not support much of the joining.

    If your child list and parent list have something on that around, that could be a problem in getting the view. else all good.


    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Tuesday, December 30, 2014 1:15 AM
  • Thanks Ashish for the quick follow up.

    So I can combine the lists via SharePoint Designer into a consolidated view but do you know if I can use the consolidated data view as a data source that has a live connection to Excel?


    Johnny

    Tuesday, December 30, 2014 1:33 AM
  • Data view will be formed in a web part.

    Try this if can help you http://blogs.msdn.com/b/mitch_tolson/archive/2013/11/13/creating-a-button-to-export-a-sharepoint-2010-data-view-webpart.aspx


    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Tuesday, December 30, 2014 2:13 AM
  • Thanks again Ashish.  I'll give it a go next week when I get access and follow up on this post.

    Johnny

    Tuesday, December 30, 2014 2:21 AM
  • Hi Johnny,

    Does the link above solve your issue? If the issue persists, please post the detail information about your issue, so that we can make further analysis.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, January 7, 2015 8:42 AM