Adding Power Pivot Data to the Data Model?

[Maggie Barr]

Hello and thank you in advance if you can help,
I am using MS Office 2019 on a PC. I am somewhat new to Power Query and Power Pivot, and I am trying to analyze a data set in a way that as much of it as possible is "automatic". I currently have to run multiple pivot tables and bring information from them, by hand, into a final summary table. I would like to add my pivot tables to the data model so that I could potentially build on the model, and thus be able to bring data from those directly into a final summary. When I go to insert a pivot table, use this workbook's Data Model, Existing Worksheet, the little box for Add this data to the Data Model is grayed out. Does anyone know why that is and how I can get the data into the model. I have all my pivot tables built, and if there were a way to select the table and add it, that would be great, but, if not, I can recreate them, it is just not giving me the option. My pivot tables do have more than one field as a header (meaning two rows), so I do not know if that will pose a problem.
Any help would be appreciated,
Thank you,
Maggie

 

[theBardd]

You should add the source of the pivots to the datamodel, not the pivots themselves. If necessary, join them in Power Pivot if they are different structures, or merge them in Power Query if they are the same.

 

[Maggie Barr]

theBardd,
Thank you for getting back to me. The data the pivot is based on is in the data model already. Now it could be that the formulas needed to create measures to come up with some of the information I need, without first running pivots, are beyond me, but I am just not able to see how I can create one pivot that does what several pivots do in regards to the analyses I have to do. If I could create a dataset off a pivot, I could then create the measures I need from those summaries to populate some of the fields in the Regional Summary table I have developed. For instance, in one case I need to run a pivot, and then I copy the pivot to a sheet and run a macro that essentially behaves like an unpivot (still trying to learn this one), and then apply filters to the results and run a count function of the remainder. If I could add the pivot to the data model, I could then interact with that data again without breaking the chain, so to speak. I had searched online, and from what I understand, there is no way to link data from a pivot directly to a data model, I only found copy pivot, paste, and add to model as the option. That is unfortunate, because having to copy the data, create a sheet from it, is a break in the chain of automaticity in the analyses that I am trying to create. Not a deal breaker, just disappointing. I will look into syntax for measures, but most of the examples I find are not what I am looking for, like to how to group a large dataset by multiple columns thus creating a count function of what is counted within those groups. Pivots are the only way I have found to do it. I have species as rows, and two column headers of region and block type, with the count of species as values, and from that I run a count function of the columns to get how many species are reported for that block type within that region, plus I bring those counts from the values into a final table and the counts from the count function I run into a header of the table with specific summary analyses. Oh bother, sorry to be rambling, and probably none of this makes sense, but, in summary, there is no way to link a pivot to the data model directly, but I want to thank for trying to help.
Best Wishes,
Maggie

 

[theBardd]

I'm sorry, but you just seem to be raising general points which are very hard to discuss without knowing the data or what your objectives are.
In addition, much of what you said makes little sense to me -

The data the pivot is based on is in the data model already.

If this is the case, if it is complete and robust data, you are good to go. What you do depends upon the analyses,

Now it could be that the formulas needed to create measures to come up with some of the information I need, without first running pivots, are beyond me, but I am just not able to see how I can create one pivot that does what several pivots do in regards to the analyses I have to do.

Why would you want one pivot to do what several existing pivots do? If those several pivots are looking at differing vies of the data that would seem the correct way to go to me. If you have those pivots, what is wrong with them?

If I could create a dataset off a pivot, I could then create the measures I need from those summaries to populate some of the fields in the Regional Summary table I have developed. For instance, in one case I need to run a pivot, and then I copy the pivot to a sheet and run a macro that essentially behaves like an unpivot (still trying to learn this one), and then apply filters to the results and run a count function of the remainder.

Power Query does an unpivot easily, but unless you have a crosstab dataset, it doesn't seem that you need to do that. Also, Power Query can be used to add extra data if that is required, not measures as such, although measures might acheive the same objective - as I said, it depends upon wht you are trying to do.

If I could add the pivot to the data model, I could then interact with that data again without breaking the chain, so to speak.

No idea what that means.

I had searched online, and from what I understand, there is no way to link data from a pivot directly to a data model, I only found copy pivot, paste, and add to model as the option. That is unfortunate, because having to copy the data, create a sheet from it, is a break in the chain of automaticity in the analyses that I am trying to create. Not a deal breaker, just disappointing.

As I keep saying, I fail to see why you would need to do this. If you have the data, work on that.

I will look into syntax for measures, but most of the examples I find are not what I am looking for, like to how to group a large dataset by multiple columns thus creating a count function of what is counted within those groups.

That sounds the sort of thing that DAX should be able to manage, but so can Power Query. How would grabbing the pivot data help there?

Pivots are the only way I have found to do it. I have species as rows, and two column headers of region and block type, with the count of species as values, and from that I run a count function of the columns to get how many species are reported for that block type within that region, plus I bring those counts from the values into a final table and the counts from the count function I run into a header of the table with specific summary analyses.

You can do that in POwer Query, albeit it is not trivial, but why bother when the pivot does it so much better?

 

[macfuller]

I may be misunderstanding your original post, but are you trying to add data in pivot tables that were sourced from the data model back into the data model? If so, that can't be done, much as all of us would like it to. The data model can only add data sourced from outside the model.

If your original post is describing something else then sorry I misunderstood.

 

[macfuller]

This is probably an imperfect analogy, but think of the data model as one of those glass boxes in the movies where the deadly virus is kept inside and you can only handle it with the rubber gloves sticking into the box (waldoes). Let's use Legos inside the box instead since it's less scary :wink:. You have 4 queries Green, Grey, Blue, and Red that put 568 total Legos into the box. DAX is the rubber gloves - you can use the Legos to build a house, car, or Millenium Falcon, but whether you use them in a measure or not there are always 568 pieces in there that cannot be added to or taken away. Queries only put Legos into the box, they can't take any away without taking them all away by altering or deleting the query. And they do all operations outside the box, only putting the finished number of Legos into the box.

Your Green query puts 51 pieces (say 51 weeks of data) into the data model. You want to split Green into Big Green and Little Green. Big Green already put 51 pieces into the data model. It's already there, visible right on the other side of the glass box. You want Little Green to add the 52nd piece to the Legos already in there. You can see the 51 pieces right there - no way I should have to run Big Green again! So you tell Little Green just to stick the 52nd Lego to the top of the 51 that are already there. But Little Green tells you it can't find the other 51 because they're not outside the box - there's nothing to stick it to unless you re-run the Big Green query to re-create the other 51.

There are probably a ton of reasons Power Query works this way... if your Big Green query ran a segment where you wanted to sum and group by the latest date in a month your prior 51 weeks would be wrong for December because you'd need the 52nd week to make your month totals right. There's no way PQ could figure out how to untangle that logic. So your attempt to use the data model tables is blocked since PQ can't take data from there.

It's agonizing to have to run the full data queries when all you want to do is append a new and smaller set of data and there's no data transformation necessary, but that's just the way it is.

And if my analogy is wrong I'm delighted to have other readers correct it!

 

[Maggie Barr]

macfuller,
Thank you, that is what I would have liked to do, and it is unfortunate that I can't, but I know how to do what I need to without it, it just would have been nice.
Best Wishes,
Maggie

 

[Maggie Barr]

theBardd,
I am sorry for that confusing ramble, and I regretted the confusion after I posted it. I cannot share my data, but I can share what the report I am producing looks like. It is very difficult to discuss fully just what goes into the report and how I get the data to populate each cell and/or column within the report. I am not an excel guru, but I do my best. I am just trying to get as close to a refresh and voilÃ*, there is the report type method as possible tp minimize the hand work. Between raw data of a couple of million records, spatially joined to ArcMap, brought into Access to manipulate, test, and filter, then brought in as a link to Power Query as a data source and running pivots from there, I think I am making a lot of progress. I just haven't learned to write DAX measures yet, or know if it can produce some of the things I need (big learning curve). I have been able to create some reference queries to the full data that can produce some of the things I need, but getting it all back in to populate the format of my report is difficult. Below is a link to an excel workbook with a few sheets. I put this there, not so much that I expect you to dive into it, but mostly to try to, hopefully, redeem myself for sounding so utterly confused/ing.
Thanks,
Maggie
https://app.box.com/s/szfp43a1vbf7w5v6poh9w50pyp65c08b

 

[macfuller]

Wouldn't the Audubon society have some tech resources that could help you? I would think what you're trying to do would be a delight for them to help you!

 

[theBardd]

Looking at your data, it seems very straightforward. Your formula is a little different than I would have used, but it works (I must admit to be intrigued that you have sightings in the future!). I still am not clear what your objective is, why you want to include pivot data into your data model. That formula is easily reproduced in Power Query. Can you elaborate on what you are trying to do, in relation to this dataset.