Power Pivot - Splitting a Table between Tabs and then merging in a pivot table

[dhsilv2]

Hey guys, I'm a pretty big novice with the power pivot tools and to be blunt, I somewhat hate pivot tables to begin with. Not here to rant, but I'm just adding that color as they tend to not be intuitive to me. If a sumifs and index and match are a 2 in easy to read and understand a pivot table is a 400. Anyway, I do somewhat like the idea behind power pivots as they feel more like a sql table and sql is much like a sumifs much easier to work with than normal pivot tables!

So I have some spread sheets I maintain just for fun with nba basketball data. Just dumps from basketball reference. Primarily they are in 3 tables. Totals, Advanced stats, and per 100 stats. I need to do some work to make these tables talk, but I think just taking the unique year and unique players and unique teams and making those independent tables will do the magic there. Anyway to my question.

I want to work with just one of those tables. It lists each player by year in a row. If I took mid year data and instead of adding to my table (which is finalized and won't change) if I made a new table with mid year data, could I join this and create a pivot against it? So for example. I have player in both tables and I want to sum a column called VORP (which is in both tables). Please let me know if you need more details.

 

[PentaGalCXO]

One of the limitations of PowerPivot - you cannot append nor replace portions of a table. With that in mind, you have two possible approaches
1) combine History with MidYear as you load the data into PowerPivot
2) maintain History and MidYear as separate tables in PowerPivot, but use DAX equations and conformed dimensions to drive your pivots

For a novice I suggest #1. I assume your source data for History is a separate file or files than MidYear. Power Query is your friend for this. There are many articles about combining/merging files with PQ. Here is one I tend to refer to most often. Once you have setup PQ, it won't matter that you are reloading both History and MidYear with every refresh. You can then also use PQ to build/load the Player/Team dimension you described.

 

[dhsilv2]

One of the limitations of PowerPivot - you cannot append nor replace portions of a table. With that in mind, you have two possible approaches
1) combine History with MidYear as you load the data into PowerPivot
2) maintain History and MidYear as separate tables in PowerPivot, but use DAX equations and conformed dimensions to drive your pivots

For a novice I suggest #1. I assume your source data for History is a separate file or files than MidYear. Power Query is your friend for this. There are many articles about combining/merging files with PQ. Here is one I tend to refer to most often. Once you have setup PQ, it won't matter that you are reloading both History and MidYear with every refresh. You can then also use PQ to build/load the Player/Team dimension you described.

The "data" source is sadly copy and paste from a website, and a LOT of it. Took me about 2 hours to get the base data, thus I would rather not merge at the data level. They should be in separate tables to avoid breaking something.

Any good guides on getting started with dax? That somewhat assumed I was asking to go that direction with the question, and to be honest this is a passion project. I have all the time in the world as this is just something I do for fun, and what person maintains tables and spread sheets and wants an easy solution ?

 

[SimonNU]

I strongly agree with PentaGalCXO. You can keep your source tables as they are (whether they be in a CSV/worksheet/whatever) but combine them during the loading phase, prior to hitting Power Pivot. Use Power Query to accomplish this ETL task.

 

[PentaGalCXO]

Sorry to hear you are limited to copy/paste do derive your data. Still... suggest you copy/paste to create multiple source Excel files... perhaps 1 file per year then one for midyear. This keeps the source data segregated so a bad copy/paste can be easily fixed. Use Power Query to combine these separate source files into a single "fact" table in PowerPivot. Use Power Query again to create the Player/Team dimension table by summarizing the same data. Add a DateDim and a couple of relationships from Fact to the Dimensions and you are well on your way.

Good guides for DAX? Fortunately there are several. Can't list them all, or even all the good ones, but I'll call out a few:
Power Pivot Blog | Tiny Lizard - Scott has been busy of late, but last year i think he answered more Power* questions than anyone else.
ExceleratorBlog - - Matt has been filling in the void left by Scott, and has also published a great book
PowerPivotPro - Transforming your Business with Power Pivot and Power BI - my long time favorite Power* blog and a couple of excellent books

Hope you enjoy the ride...

 

[dhsilv2]

Sorry to hear you are limited to copy/paste do derive your data. Still... suggest you copy/paste to create multiple source Excel files... perhaps 1 file per year then one for midyear. This keeps the source data segregated so a bad copy/paste can be easily fixed. Use Power Query to combine these separate source files into a single "fact" table in PowerPivot. Use Power Query again to create the Player/Team dimension table by summarizing the same data. Add a DateDim and a couple of relationships from Fact to the Dimensions and you are well on your way.

Good guides for DAX? Fortunately there are several. Can't list them all, or even all the good ones, but I'll call out a few:
Power Pivot Blog | Tiny Lizard - Scott has been busy of late, but last year i think he answered more Power* questions than anyone else.
ExceleratorBlog - - Matt has been filling in the void left by Scott, and has also published a great book
PowerPivotPro - Transforming your Business with Power Pivot and Power BI - my long time favorite Power* blog and a couple of excellent books

Hope you enjoy the ride...

Thanks. We have about 75 years in the nba, so that would be rather excessive with one table per. I copy and paste the data by year and then add the year to it as a column. I'd rather keep it this way, it's a lot easier for *most* things to write formulas and build tables for reports vs dealing with the cumbersome and a bit confusing pivot tables. Plus who doesn't like writing long arrays? However, it seems a database approach with separate tables would be easier here if I'm going to have a "temp table" I'll want to write something against and then kill from time to time.

However it sounds like you're saying what I want it possible. A large "master" table with the temp table merged with some of this dax magic. .

The other alternative would be to do all this in access, and spit out the merged data, but as I like to modify columns a bit more free form than access likes, I'd rather avoid moving this to an actual database. I'm hoping (I've only used power pivot minimally as the vast majority of data I have historically used was many to many and until recently I'd been told power pivot did not support that well, turns out there are work around) power pivot gives me some of the advantages of sql/relational databases with the free form editing ability of excel.

 

[dhsilv2]

I strongly agree with PentaGalCXO. You can keep your source tables as they are (whether they be in a CSV/worksheet/whatever) but combine them during the loading phase, prior to hitting Power Pivot. Use Power Query to accomplish this ETL task.

So if I have my primary table on sheet 1 and temp data on sheet two, I could do something with power query there? I do NOT want to work with outside sources. Mostly because I'm much better at organizing a spread sheet than a folder.