equivalent of MS Access joins

[illusionek]

Hello

Few years ago I was using Access quite a bit. I used to bring various Excel files into MS Access join them using inner/outer joins and then create a query that was used to feed Excel file with all data. It worked smoothly and was easier/faster than VBA.

I made couple attempts in the past to do something similar in Data Model with PowerPivot but it never seems to be working, I keep coming across various issues and just give up. Is there any good tutorial, book, articles I could use that would cover this topic thoroughly?

I would need to bring values from multiple tables to create measures and also join tables using multiple fields. All of that was easy/possible in Access but I struggle to replicate it with PowerPivot. I was wondering if I perhaps should use PowerQuery on that but couldnt find info whether joins from PowerQuery would go into the Data Model for PowerPivot

 

[GraH]

Hi

Some quick replies:
- PowerPivot data model in Excel only knows many-to-one relationships. From fact tables to dimension tables.
- Power Query knows: left outer, left anti, inner, full outer, right outer, right anti join types. When joining, you need to extend the joined data. And so you are creating flat tables.
- Power Query can sent data to the data model.

Google Matt Allington, Rob Collie, Marco Russo and Alberto Ferrari. For sure plenty of solid material to read and learn from. On YouTube look for Mr Excel, Mike "ExcelIsFun" Girvin, Excel On Fire (Oz du Soleil), PowerPivotPro, Goodly,... And an all bunch of others I'm forgetting about right now.