Excel Business Analytics #39: Import Related Tables: Power Query Merge? Data Model Relationships?

Learn about:
1) (00:04) Download File Information
2) (00:21) Look at Transaction table in the Text file and the lookup table in the Excel file.
3) (00:40) Goal of video: create Regional with fields from both tables. Discuss how we will accomplish this and discuss how the two tables are related.
4) (02:00) Preview of two methods: 1) Import Two Tables into Data Model and create Relationship from PivotTable Field List or Import Two tables and use Power Query Merge Tables to create a transaction table with a lookup column
5) (02:47) Method 1: Import Two Tables into Data Model: Text Transaction Table and Excel Lookup table
6) (04:10) Method 1: Access the Data Model that contains the two tables using the Create PivotTable dialog box and “Use an external data source” and “Choose Connection” button
7) (04:47) Method 1: Create a relationship between the tables using the Create Relationship Yellow Button in the PivotTable Field List
8) (08:06) Method2: Merge Tables in Power Query to simulate a helper column VLOOKUP function formulas.
9) (09:21) Method2: In Power Query Editor, Expand new table column and choose to show only Region Column.
10) (10:05) Method2: Close and load new Merged table to the worksheet.
11) (10:22) Method2: Edit Merged Query
12) (10:53) Create PivotTable from new Merged Table
13) (11:20) Summary and Conclusion
Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.

Get Two Tables into an Excel 2013 PivotTable Field List without using PowerPivot AND Replace VLOOKUP: Power Query Merge? Data Model Relationships?
How to indirectly access Data Model and Relationships in Excel 2013 PivotTable Field List without using PowerPivot

[joshuabradshaw]

Most thorough Excel Channel. You make me look like a rock star at work. I always give credit to the world-wide-web and all of the experts, and one of them is you! Thank you for your contribution to the internet. It truly is making a positive impact. Bless you, sir!

[Luciano_mp]

Espetacular suas aulas, uma série incrível, você faz um trabalho grandioso com suas aulas. Assim como eu, acredito que seus internautas ficam maravilhados com seus ensinamentos e disposição. Um grande abraço...Muito Obrigado.

[leonardosantiago]

Your videos are just amazing!!!! Thanks for sharing your knowledge. Greetings from Ecuador.

[masterof]

Excellent - your video from 2 years ago answered my question yesterday! Thank you!

[humbertosoto]

Your videos are all amazing ...Thanks ... Now I feel I am able to use Power Query at an advanced level. .... Awesome!

[OzduSoleilDATA]

Thanks for the easy way to remember that the lookup table needs to be on the bottom of the relationship dialog box.

[jorgeluissupelanor.]

Hi Mike. I just discovered these Business Analytics series. Great, great video and a much needed playlist (at least for me). You should update your Power Query version so when you merge tables you can choose what type of join to apply (left, right, inner, etc.). Really powerful

[LearnAccessByCrystal]

awesome! Thank you, Mike. Excel is starting to get more of Access :) ~ even Relationships and Data Types!

[BillSzysz]

Thanks Mike :-))
if you want the sum in regions only, you don't need build Pivot Table. Use Grouping by region column in PQ.
Relationships within PQ works perfectly also for more than one column.

[tomwaldhutter]

Hi, nice video? do you know, if there are record restrictions in case of merging data from MySQL, for example when I have more that 5.000.000 records in those database tables? Thanks and have a nice day!

[patrykpopioek]

Great videos! Love to learn from you.
I'm just wondering why you're not using shortcuts to change Formats (Ctrl + Shift + 1 = numbers, ...+ 3 = date, ...+ 4 = currency, ...+ 5 = %)

[tomash]

Great Mike ! Imho, What about the serie with Mr. Excel ? Is it finished or just waiting for inspiration ? :)

[BoxerDogs]

Thank you for posting this video.  I'm new to this Query Editor and Power Query stuff.  I have two questions:

1.  How do you get the Power Query tab to display on the ribbon?

[bukkfenc]

Useful and good stuff, but soo arrogant tone :/