Join multiple tables in a PivotTable

No video

Join multiple tables in a PivotTable

Рет қаралды 81,284

Excel Off The Grid

Күн бұрын

Пікірлер: 33

@sachinkishore4115 18 күн бұрын

Thank you very much

@ExcelOffTheGrid 16 күн бұрын

You are welcome

@tinkytinkz Жыл бұрын

Thanks Mark! First time I've learned primary/foreign

@ExcelOffTheGrid Жыл бұрын

Thanks, glad I could help you pick up some new terms. 😀

@wayneedmondson1065 3 жыл бұрын

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!!

@ExcelOffTheGrid 3 жыл бұрын

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.

@wayneedmondson1065 3 жыл бұрын

@@ExcelOffTheGrid Thanks! Nice to know both ways :)) Cheers!!

@mariamelaniereyes2691 3 жыл бұрын

Thank you Mark, your video uploads are gifts of knowledge.

@ExcelOffTheGrid 3 жыл бұрын

Thank you. That's very kind of you to say :-)

@NataliaGarcia-wk9xk Жыл бұрын

GREAT TUTORIAL! THANK YOU!!

@ExcelOffTheGrid Жыл бұрын

Thank you 😀

@marglego9079 3 жыл бұрын

Tutorial, perfect n clear, THANKS!

@ExcelOffTheGrid 3 жыл бұрын

You're welcome!

@sallyho146 Жыл бұрын

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.

@deerich188 2 жыл бұрын

Helped, thank you!

@ExcelOffTheGrid 2 жыл бұрын

You're welcome!

@nsanch0181 3 жыл бұрын

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.

@ExcelOffTheGrid 3 жыл бұрын

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 :-)

@lpanades 3 жыл бұрын

Very nice

@ExcelOffTheGrid 3 жыл бұрын

Thank you 😀

@lolmanez2525 Жыл бұрын

Bravo!!!

@ExcelOffTheGrid Жыл бұрын

Thank you.

@godsonsoans3897 Жыл бұрын

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.

@ExcelOffTheGrid Жыл бұрын

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.

@jeffedelstein308 Жыл бұрын

is there a workaround for a mac? Powerpivot oand data models are not available on mac.

@ExcelOffTheGrid Жыл бұрын

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.

@mirrrvelll5164 3 жыл бұрын

Good but not every version of Excel has Power Pivot...but definitely great thing.

@ExcelOffTheGrid 3 жыл бұрын

Unfortunately Mac and Online are not quite there yet. Maybe one day :-)

@megatakief1283 2 жыл бұрын

@@ExcelOffTheGrid is there any other way to join multiple tables into a pivot table for mac?

@lilyiann 2 жыл бұрын

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

@ExcelOffTheGrid 2 жыл бұрын

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.