Extract static data from data models in other Power Pivot workbooks

andredl
Posts: 61
Joined: Wed Sep 23, 2015 9:56 pm

Extract static data from data models in other Power Pivot workbooks

Postby andredl » Mon Aug 02, 2021 9:48 am

Hi,

Does anyone know of a way to extract static data that has been loaded into a power pivot data model in one workbook, into a separate workbook, without refreshing the original query that was the source of the data in the first workbook?

I have set up a scheduled refresh of a query to run and save a separate workbook with that static data each time it runs, but the data isn't displayed in any of the sheets in the "front end" of Excel. It is just saved in the power pivot data model, and the next time it runs, the workbook is saved with a date stamp in its name to differentiate it from the previous query run.

I want to report on how the data changed over time, without having to re-run the queries that extracted the original data and without having to pivot up the results in one of the sheets so as to be visible in the front end, as there are hundreds of workbooks I wish to extract static data from.

Any time-saving thoughts out there?

Thanks,
André

PhilC
Posts: 282
Joined: Tue Sep 09, 2014 8:13 am

Re: Extract static data from data models in other Power Pivot workbooks

Postby PhilC » Wed Aug 04, 2021 9:14 am

Not looking good Andre.

It doesn't look like a connection can be made to the data model in Excel. I suppose that does make a bit of sense as it is not one flat dataset.

Not ideal, but can you go into each file and push the data you need to be able to review to a table, then use that? Might not work if refreshing the file looses the data as at a historical point.

Understand there are a lot of historical files to do this for, but can have it so new ones already have they when the data is refreshed, so it is a one time exercise for all the current historical files.

Let us know if you do find a solution though.

Cheers
Phil

andredl
Posts: 61
Joined: Wed Sep 23, 2015 9:56 pm

Re: Extract static data from data models in other Power Pivot workbooks

Postby andredl » Tue Aug 10, 2021 12:32 pm

Thanks Phil, yes this is the problem I'm facing. I know how to do it if I go into each of the files, pivot up the results in the data model to be visible in a pivot table, then query the sheets from a different workbook, but I was hoping there's a less tedious solution out there. Given how easy PQ makes a lot of tasks, the lack of this functionality is surprising to me.

Ingrid
Posts: 1
Joined: Tue Sep 14, 2021 4:41 am

Re: Extract static data from data models in other Power Pivot workbooks

Postby Ingrid » Mon Sep 20, 2021 3:27 pm

You should be able to make a second table in the Data model that lists the People in one column and their respective group in the second column.

Then go Data - Manage Data Model, and under 'Design' select 'Create Relationship' and create a relationship between the names list in the table you just created with the list of names in your data. This will create a one to many relationship.

Then in your pivot tables you can pull in the groups or names of the people from the table you just created and the other data you need from your main data set and excel will be able to match up the data via that relationship.

Sorry if that's not explained well, it's hard without seeing some sample data.


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 0 guests