Excel - Creating Relationships in PowerPivot - Episode 1229 скачать в хорошем качестве

Excel - Creating Relationships in PowerPivot - Episode 1229

Microsoft Excel Tutorial: Episode #1229 shows you how to manually define a relationship between two tables in PowerPivot. Welcome back to the MrExcel netcast. In this episode, we will be discussing how to manually define relationships in PowerPivot. While Excel does have the option to auto-detect relationships, it's always better to be safe than sorry and manually define them. Luckily, it's a simple process that we will walk through together. First, let's take a look at our data. We have a 1.8 million row data set with a field called Store ID, and a 144 row lookup table with a field called Store. Each Store ID in the larger data set corresponds to a store in the lookup table, creating a one-to-one relationship. In the past, joining this data together would have required numerous VLOOKUPs, making the calculation process extremely time-consuming. But with PowerPivot, it's a breeze. To define the relationship, we start by right-clicking on the Store ID field and selecting "Create Relationship." Since we started on that field, it is automatically filled in as the starting point. Then, we choose the related lookup table, in this case, "store info," and select the Store field. With just a click of a button, the two tables are now joined together, allowing us to easily create pivot tables and use the related function to create calculated columns. Thank you for tuning in to this netcast from MrExcel. Don't forget to check out Easy-XL PowerPivot for the Data Analyst to make your data analysis even more efficient. See you next time for another informative episode. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-... Table of Contents: (00:00) PowerPivot - Defining Relationships (00:15) Manually Defining a Relationship (00:25) Auto-Detecting vs. Manual Definition (00:35) Example Data Set (00:45) One-to-One Relationship (00:58) Easy Definition Process (01:08) Starting on Store ID Field (01:18) Creating the Relationship (01:36) Benefits of Joining Tables (01:52) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: 1.8 Million Road data set Auto-detect relationship in Excel Defining relationships Joining tables in PowerPivot Look up table Manually defining a relationship One-to-one relationship Pivot table Store ID VLOOKUPs Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...