Data Model an easy substitute for Vlookup

you have a data set with product, date, customer, and sales information.

A data set wth Product, Date, Customer, and Quantity.

The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector. Time for a VLOOKUP, right?

A second data set is a lookup table, mapping Customer to Industry Sector. Both data sets should be formatted as a table using Ctrl+T. Use the Table Name box on the Table Tools tab of the Ribbon to assign a name such as Sectors to this table.

There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or newer. These versions of Excel have incorporated the Power Pivot engine into the core Excel. (You could also do this by using the Power Pivot add-in for Excel 2010, but there are a few extra steps.)

In both the original data set and the lookup table, use Home, Format as Table. On the Table Tools tab, rename the table from Table1 to something meaningful. I’ve used Data and Sectors.

Select one cell in the data table. Choose Insert, Pivot Table. Starting in Excel 2013, there is an extra box, Add This Data to the Data Model, that you should select before clicking OK.

As you create the pivot table, the last choice in the Create PivotTable dialog is Add This Data To The Data Model.

The Pivot Table Fields list appears, with the fields from the Data table. Choose Revenue. Because you are using the Data Model, a new line appears at the top of the list, offering Active or All. Click All.

Surprisingly, the PivotTable Fields list offers all the other tables in the workbook. This is ground-breaking. You haven’t done a VLOOKUP yet. Expand the Sectors table and choose Sector. Two things happen to warn you that there is a problem.

The PivotTable Fields pane now offers a choice at the top for Active, or All.

First, the pivot table appears with the same number in all the cells.

Something is wrong. Every industry sold exactly $6.7 Million.

Perhaps the more subtle warning is a yellow box that appears at the top of the PivotTable Fields list, indicating that you need to create a relationship. Choose Create. (If you are in Excel 2010 or 2016, try your luck with Auto-Detect - it often succeeds.)

A yellow warning at the top of the PivotTable Fields says "Relationship Between Tables May Be Needed". Choose Auto-Detect or Create.

In the Create Relationship dialog, you have four dropdown menus. Choose Data under Table, Customer under Column (Foreign), and Sectors under Related Table. Power Pivot will automatically fill in the matching column under Related Column (Primary). Click OK.

There are four settings to define a relationship. The table called Data has a field called Customer. The Related Table called Sectors has a field called Customer.

The resulting pivot table is a mash up of the original data and the data in the lookup table. No VLOOKUPs required.

The pivot table is now reporting Sector from Sheet2 and Revenue from Sheet1, thanks to the Data Model and the Relationship.

Comments

20 Power BI Dax Measures

Power bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples: Total Sales: scss Copy code Total Sales = SUM (Sales[Amount]) Average Sales Price: scss Copy code Avg Sales Price = AVERAGE (Sales[Amount]) Total Units Sold: mathematica Copy code Total Units Sold = SUM ( Sales [ Quantity ] ) Total Customers: scss Copy code Total Customers = COUNTROWS (Customer) Total Products: mathematica Copy code Total Products = COUNTROWS ( Product ) Maximum Sales Amount: scss Copy code Max Sales Amount = MAX (Sales[Amount]) Minimum Sales Amount: scss Copy code Min Sales Amount = MIN (Sales[Amount]) Sales Growth Percentage: mathematica Copy code Sales Growth % = ( Total Sales - [ Total Sales Last Year ] ) / [ Total Sales Last Year ] Total Profit: scss Copy code Total Profit = SUM (Sales[Profit]) Total Orders: scss Copy code Total Orders = COUNTROWS (Orders) Total Customers with Sales: css Copy code Total Customers with Sales = COUNTROWS( FILTER ...

Error Handling

Formula errors are common. If you have a data set with hundreds of records, a divide-by-zero and an #N/A errors are bound to pop up now and then. In the past, preventing errors required Herculean efforts. Nod your head knowingly if you’ve ever knocked out =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)) . Besides being really long to type, that solution requires twice as many VLOOKUPs. First, you do a VLOOKUP to see if the VLOOKUP is going to produce an error. Then you do the same VLOOKUP again to get the non-error result. Excel 2010 introduced the greatly improved =IFERROR(Formula,Value If Error) . I know that IFERROR sounds like the old ISERROR, ISERR, and ISNA functions, but it is completely different. This is a brilliant function: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found") . If you have 1,000 VLOOKUPs and only 5 return #N/A, then the 995 that worked require only a single VLOOKUP. Only the 5 VLOOKUPs returned #N/A that need to move on to th...

Basics Of Excel

MS Excel is a massive application, so you need to know the basics which will help you to navigate Excel and access features. Below is the Image of Excel when you open it. There are 5 important areas in the screen. 1. Quick Access Toolbar: This is a place where all the important tools can be placed. When you start Excel for the very first time, it has only 3 icons (Save, Undo, Redo). But you can add any feature of Excel to to Quick Access Toolbar so that you can easily access it from anywhere (hence the name). 2. Ribbon: Ribbon is like an expanded menu. It depicts all the features of Excel in easy to understand form. Since Excel has 1000s of features, they are grouped in to several ribbons. The most important ribbons are – Home, Insert, Formulas, Page Layout & Data. 3. Formula Bar: This is where any calculations or formulas you write will appear. You will understand the relevance of it once you start building formulas. 4. Spreadsheet Grid: This is where all yo...

Solutions for Data Science

Search This Blog