Join multiple tables in a PivotTable
HTML-код
- Опубликовано: 22 июл 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Download the example file:★
exceloffthegrid.com/combining...
★ About this video ★
When most people use PivotTables, they copy the source data into a worksheet, then carry out a lot of VLOOKUPs to get the categorization columns into the data set. After that, the data is ready, we can create a PivotTable, and the analysis can start.
But we don’t need to do all those VLOOKUPs anymore. Instead, we can build relationships that combine multiple tables and automatically create the lookups for us.
In this video, I will show you how :-)
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #PivotTables #PowerPivot
Thank you Mark, your video uploads are gifts of knowledge.
Thank you. That's very kind of you to say :-)
Hi Mark. Great tutorial! My first pass at it was to load the three tables into Power Query and then merge twice to a new combined query using Left Outer joins on Sales Data with SalesRepID and ProductID.. then close and load to a Pivot Table Report. Now I see this was much more work than just setting up the relationships through the relationship manager or directly with Power Pivot and Diagram View. Thanks for the great tips and inspiration to learn and practice a new method :)) Thumbs up!!
The Power Query method is the correct way to do it. Go with that if you already know how to. 😀
But since many people don’t know about Power Pivot, then this was my sneaky way of introducing it, and trying to make it seem less scary.
@@ExcelOffTheGrid Thanks! Nice to know both ways :)) Cheers!!
Thanks Mark! First time I've learned primary/foreign
Thanks, glad I could help you pick up some new terms. 😀
Tutorial, perfect n clear, THANKS!
You're welcome!
GREAT TUTORIAL! THANK YOU!!
Thank you 😀
Helped, thank you!
You're welcome!
I got a message that says duplicate values. I didn’t know how to fix it. Can you explain more on the meaning of duplicate values.
Thank you Mark for the great video. That is neat relationships can be made without even going into Power Pivot. I wonder if there is any faster way to get the 3 tables into the model; Or do you have to go through the steps of making tables then forming relationships. Not that it's too much work.
This is probably the fastest way. But I doubt it is speed that we are looking for in most circumstances.
To build robust flexible models, then going through Power Query into the data model is by far the best option.
I’m hoping this video opens up Power Pivot and the concept of relationships to more people :-)
Hi! Thanks for a great video! Is it possible to do a many-to-many join using this method? For example, one table is sales of pastries (columns: date, item, quantity). The second table is list of ingredients (columns: item, ingredient). I'd like to left outer join these two tables on item, to get a view of how many ingredients are used based on sales
This video shows a simple approach to joining Tables, so you can't do a many-to-many directly with this method.
There is a left outer join in Power Query which could achieve what you are looking for easily.
Bravo!!!
Thank you.
Very nice
Thank you 😀
Hello Mark, I have one question.
I have created 2 tables on the Excel sheet (I have labelled them as week 1 & week 2 table respectively). both the tables have same headings but the data inside the table is different. I'm trying to filter or use a Pivot table where i can filter one table at a time. For eg: If i have created 10 Tables in a worksheet all with the same headings which would be labelled Week 1, week 2, week 3..... and so on till 10. how can i filter to show only one of the tables on the excel sheet ? Please help.
I would recommend using Power Query to combine the Tables into a single Table where the first column is the week number
Once you’ve achieved that you can do almost anything you like with the data.
is there a workaround for a mac? Powerpivot oand data models are not available on mac.
There is nothing native on the Mac or Excel Online.
Unfortunately I think the only option is a running an instance of windows on the Mac. Sorry.
Good but not every version of Excel has Power Pivot...but definitely great thing.
Unfortunately Mac and Online are not quite there yet. Maybe one day :-)
@@ExcelOffTheGrid is there any other way to join multiple tables into a pivot table for mac?
First time ever I'm first!
If you watch videos on double speed, you can be first more often :-)