Summary
Keywords
Full Transcript
Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/AllFilesBI348Analytics.htm 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
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
