Yesterday, in episode 1733, it required two MATCH and one INDEX to merge the data from two worksheets. Today, using PowerPivot in Excel 2010, another way to find the price for each customer/item combination. While PowerPivot is supposed to make life easier, this method still requires two concatenation formulas, two Ctrl+T, two Add Linked Table, one drag from Key to Key, then one RELATED function and an application of Mike Alexander's trick from my episode 493. Which is easier? 1733 or 1734? Make your note in the comments below.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1734 - Price Table Lookup Using PowerPivot!
Well hey, if you saw yesterday's episode 1733, we had a mash up data on the sold sheet, which has customer item in quantity, with a price list which has a different price for every single customer. And I did that using a couple of MATCHes and an INDEX, said "I could have done it in PowerPivot." And when I posted YouTube, Mike posted a note, and said "Hey, it would be interesting to see how you do that in PowerPivot, just as a comparison." Alright, so yesterday we learned that the data was completely mismatched, the items, numbers here didn't match the items across the top. But now we know that they're all exactly correct, so that helps. I'm glad that I went through the hard method yesterday using INDEX and MATCH because that allowed me to discover all the mismatches. I'm going to add one more field here called key, and that key is going to be =customer&item. And I'm going to use that to be the link in PowerPivot.
Alright, so we have our data here, I'm going to make this into a table with Ctrl+T, click OK.
I'm going to be a geek here and call this Fact, that's what SQL Server guys call their main table, and under PowerPivot I am going to create a linked table. So now that my sales data is in PowerPivot. OK here we are in PowerPivot, I can see that my display drivers are- the computer's probably ready to crash, but we're going to keep going anyway, and we'll just return back to Excel here. Alright, it's going to be a mistake, I'm sure, alright. This data we have to unwind, we have repeating groups that are going across the column, so those really need to go down. We're going to use the great Mike Alexander trick, if you don't know Mike, go to DataPigTechnologies.com.
So we're going to create an old fashioned multiple consolidation range Pivot table from this data. Alt D P, Multiple consolidation ranges, click Next. I will create the page fields, click Next, my data is here, click Add, no page fields, click Finish. We get a brand new pivot table that is exactly like the original data.
This is the point that it's ready to strangle Mike, it's like "What good is that?" Well good is that you go to the grand total, grand total, and double click, and you get a table that is your original data, but it is unwound like this. Ctrl+C, Alt E S, we need to convert to Values, this is customer, this is item, this is price. And then we have still have our key field, right, here. So key is going to be =customer&item, double click to shoot that down. Alright, so we don't even need this Pivot table anymore, we can get rid of those columns, delete, and we will make this into a table, Ctrl+T, click OK. Let's call this PriceTable, good. And we are going to add that to PowerPivot, create a link table.
So now we have two items, we have Fact and we have PriceTable.
I'm going to explicitly explain the relationship here, so we go from our key column on the Diagram View, and I can simply drag from the key column in this table, to the key column in that table to create the link, there we go. Let's go back to our Data View and go to my Fact table, to create a column that is revenue, that is going to be =Fact[ Quantity ]*RELATED!
RELATED is like VLOOKUP in regular Excel, but we don't have to specify the whole bit here. So that one is the PriceTable[ Price ], there we go. OK, so RELATED says "Hey, just follow the relationship that I've already defined, and go over and get the price for this customer, this item." Press Enter there, go through and calculate all of that, right click and say Rename, and I'll just call it Revenue, very good. PivotTable, on a New Worksheet, and then choose customer and revenue, and we are good to go, so. Without having to do a lot of VLOOKUPSs there, it's a lot simpler to create the relationship in PowerPivot.
Just drag from one field to the other, and then use the RELATED function, much simpler than using MATCH and MATCH and INDEX.
So there you go, thanks to Mike for suggesting that I solve this exact same problem using PowerPivot. There was that extra step to take the price list and create that multiple consolidation range pivot table. And then double-click the grand total, grand total, in order to unwind that data, the items that had been going across are now going down.
Alright hey, I want to thank you for stopping by, for more about PowerPivot check out my book "PowerPivot for the Data Analyst", see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1734 - Price Table Lookup Using PowerPivot!
Well hey, if you saw yesterday's episode 1733, we had a mash up data on the sold sheet, which has customer item in quantity, with a price list which has a different price for every single customer. And I did that using a couple of MATCHes and an INDEX, said "I could have done it in PowerPivot." And when I posted YouTube, Mike posted a note, and said "Hey, it would be interesting to see how you do that in PowerPivot, just as a comparison." Alright, so yesterday we learned that the data was completely mismatched, the items, numbers here didn't match the items across the top. But now we know that they're all exactly correct, so that helps. I'm glad that I went through the hard method yesterday using INDEX and MATCH because that allowed me to discover all the mismatches. I'm going to add one more field here called key, and that key is going to be =customer&item. And I'm going to use that to be the link in PowerPivot.
Alright, so we have our data here, I'm going to make this into a table with Ctrl+T, click OK.
I'm going to be a geek here and call this Fact, that's what SQL Server guys call their main table, and under PowerPivot I am going to create a linked table. So now that my sales data is in PowerPivot. OK here we are in PowerPivot, I can see that my display drivers are- the computer's probably ready to crash, but we're going to keep going anyway, and we'll just return back to Excel here. Alright, it's going to be a mistake, I'm sure, alright. This data we have to unwind, we have repeating groups that are going across the column, so those really need to go down. We're going to use the great Mike Alexander trick, if you don't know Mike, go to DataPigTechnologies.com.
So we're going to create an old fashioned multiple consolidation range Pivot table from this data. Alt D P, Multiple consolidation ranges, click Next. I will create the page fields, click Next, my data is here, click Add, no page fields, click Finish. We get a brand new pivot table that is exactly like the original data.
This is the point that it's ready to strangle Mike, it's like "What good is that?" Well good is that you go to the grand total, grand total, and double click, and you get a table that is your original data, but it is unwound like this. Ctrl+C, Alt E S, we need to convert to Values, this is customer, this is item, this is price. And then we have still have our key field, right, here. So key is going to be =customer&item, double click to shoot that down. Alright, so we don't even need this Pivot table anymore, we can get rid of those columns, delete, and we will make this into a table, Ctrl+T, click OK. Let's call this PriceTable, good. And we are going to add that to PowerPivot, create a link table.
So now we have two items, we have Fact and we have PriceTable.
I'm going to explicitly explain the relationship here, so we go from our key column on the Diagram View, and I can simply drag from the key column in this table, to the key column in that table to create the link, there we go. Let's go back to our Data View and go to my Fact table, to create a column that is revenue, that is going to be =Fact[ Quantity ]*RELATED!
RELATED is like VLOOKUP in regular Excel, but we don't have to specify the whole bit here. So that one is the PriceTable[ Price ], there we go. OK, so RELATED says "Hey, just follow the relationship that I've already defined, and go over and get the price for this customer, this item." Press Enter there, go through and calculate all of that, right click and say Rename, and I'll just call it Revenue, very good. PivotTable, on a New Worksheet, and then choose customer and revenue, and we are good to go, so. Without having to do a lot of VLOOKUPSs there, it's a lot simpler to create the relationship in PowerPivot.
Just drag from one field to the other, and then use the RELATED function, much simpler than using MATCH and MATCH and INDEX.
So there you go, thanks to Mike for suggesting that I solve this exact same problem using PowerPivot. There was that extra step to take the price list and create that multiple consolidation range pivot table. And then double-click the grand total, grand total, in order to unwind that data, the items that had been going across are now going down.
Alright hey, I want to thank you for stopping by, for more about PowerPivot check out my book "PowerPivot for the Data Analyst", see you next time for another netcast from MrExcel!
