Excel get data from data Model

If you want to get data from Power Pivot data model, you don´t have too many options to do it. The most direct way is to use the Pivot Table, but often it doesn´t suit your demands. In this situations you can use the CUBEVALUE function.

In this example we will use this sales data sample. 

We want to get the total revenue for Mondays (and we already have the calculated measure Total revenue).

Lets go to sheet (in the same file where the data model is) and write this function:

• =CUBEVALUE("ThisWorkbookDataModel";"[Total revenue]";"[Weekday].[Friday]]")

Explanation:

• Cubevalue is a name of function
• "ThisWorkbookDataModel" is a name of connection. If you are not sure, check it in Data / Connections. 
• "[Total revenue]" is a name of measure, that has been used in Data Model (it is not working for common summary of column - you have to create the classical explicit measure). 
• "[Weekday].[Friday]]" because we don´t want all revenue - we only want Fridays. 

The arguments of this function can be taken from cell - just keep in mind that you need to connect it all together by CONCATENATE or ampersand... 

I'm using an up-to-date version of Excel 2016 (via O365 E3 license) and using Power Query / Get & Transform Data. I can successfully create queries and load them to the page. I have also successfully created Power Pivot reports.

I would like to query single data points from the data loaded via Power Query. For instance, imagine a dataset called DivisionalRevenue with:

Date        Division    Revenue
2016-01-01  Alpha       1000
2016-01-02  Alpha       1500
2016-01-01  Beta        2000
2016-01-02  Beta         400

I could easily load that to an Excel workbook or include it in the data model and create a power pivot. However, Power Pivot doesn't always meet my requirements, particularly around how the data is displayed on the page. In order to achieve my goal I may want to be able to query individual data points.

I would like to have a cell on the page with a formula in it that I can use to query individual data points. If it was in a pivot table I could use something like:

=GETPIVOTDATA("Revenue",$A$3,"Date",DATE(2016,1,1),"Division","Alpha")

The lookup values (date and division) could be retrieved from a cell on the page or hard-coded into the formula. This is a requirement for several reports I'm working on.

Or, I could add a combined lookup column with Date and Division concatenated and use a vlookup to pull the values like:

=VLOOKUP("42371Alpha",I9:L13,4,FALSE)

Finally, I could use a combination of INDEX and MATCH to identify the correct row number and then pull the data.

All of these solutions require the data to be loaded onto a sheet. One requires a pivot table that has to be refreshed to work properly. The other two require creating arbitrary lookup columns so that you can match a row based on more than one field (date and division in this example), and you have to ensure that that lookup field's formula is properly extended down the length of the data table. In both cases I would have concerns when sharing this workbook with my colleagues in case someone affects the rather fragile setup of the pivot table or the lookup.

So, what I truly want to find is something equivalent to pivot table querying against a dataset.

** This doesn't exist, but I would like to know if something like it does **
=GETQUERYDATA("Revenue","DivisionalRevenue","Date",DATE(2016,1,1),"Division","Alpha")

Does such a thing exist? Can such a thing be done? Can I retrieve arbitrary data points from the dataset created through Power Query / Get & Transform Data?

The data model in Excel is a type of data table where two or more two tables are in a relationship with each other through a common or more data series. In the data model, tables and data from various other sheets or sources come together to form a unique table that can access the data from all the tables.

For example, a data table containing tables of customers, product items, and product sellers. We can use the Excel data model to connect this dataset and create a relationship between them.

Table of contents

Explanation

• It allows integrating data from multiple tables by creating relationships based on a common column.
• Data models are used transparently, providing tabular data that can be used in a Pivot Table in ExcelPivot Table In ExcelA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more and Pivot Charts in excelPivot Charts In ExcelIn Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends.read more. In addition, it integrates the tables, enabling extensive analysis using pivot tables, power pivot, and Power View in ExcelPower View In ExcelExcel Power View is a data visualization technology that helps you create interactive visuals like graphs, charts. It allows you to analyze data by looking at the visuals you created. As a result, it makes your excel data more meaningful and insightful for better decision making.read more.
• The data model allows loading data into Excel’s memory.
• It is saved in memory, where we cannot directly see it. Then We can instruct Excel to relate data to each other using a common column. The ‘Model’ part of the data model refers to how all tables relate.
• The data model can access all the information it needs, even in multiple tables. After the data model is created, Excel has the data available in its memory. With the data in its memory, we can access the data in many ways.

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Data Model in Excel (wallstreetmojo.com)

Examples

You can download this Data Model Excel Template here – 

Example #1

We have three datasets related to the salesperson: the first contains revenue information, the second includes the salesperson’s income and the third consists of the expenses of the salesperson.

To connect these three datasets and make a relationship with them, we make a data model with the following steps:

• Convert the datasets to table objects:

We cannot create a relationship with ordinary datasets. The data model works with only Excel TablesExcel TablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more objects. To do this:

• Step 1 – We must first click anywhere inside the dataset, click on the “Insert” tab, and click on “Table” in the “Tables” group.

• Step 2 – Check or uncheck the ‘My table has headers’ option and click “OK.”

• Step 3 – We must enter the table’s name in the “Table Name” in the “Tools” group with the new table selected.

• Step 4 – Now, we can see that the first dataset is converted to a “Table” object. On repeating these steps for the other two datasets, we know that they also get converted to “Table” objects as below:

Adding the “Table” objects to the Data Model: Via Connections or Relationships.

Via Connections

• We must select one table, click on the “Data” tab, and click on “Connections.”

• There is an icon for “Add.” Expand the dropdown of “Add” and click on “Add to the Data Model” in the resulting dialog box.

• Click on “Tables” in the resulting dialog box, select one of the tables, and click “Open.”

In doing this, it would create a workbook data model with one table, and a dialog box would appear as follows:

So if we repeat these steps for the other two tables, the data model will contain all three tables.

We can now see that all three tables appear in the “Workbook Connections.”

Via Relationships

Create the relationship: Once both the datasets are table objects, we can create a relationship between them. To do this:

• First, we should click on the “Data” tab and “Relationships.”

• As a result, we can see an empty dialog box with no current connections.

• Then, click on “New,” and another dialog box appears.

• Expand the “Table” and “Related Table” dropdowns: the “Create Relationship” dialog box appears to pick the tables and columns to use for a relationship. In the expansion of “Tables,” we must select the dataset we wish to analyze somehow, and in “Related Table,” we must choose the dataset with LOOKUP values.
• The lookup table in excelLookup Table In ExcelLookup tables are simply named tables that are used in combination with the VLOOKUP function to find any data in a large data set. We can select the table and name it, and then type the table's name instead of the reference to look up the value.read more is the smaller table in the case of one to many relationships. It contains no repeated values in the common column. In expanding “Column (Foreign),” we must select the common column in the main table. In “Related Column (Primary),” we must choose the common column in the related table.

• With all these four settings selected, click on “OK.” A dialog box appears as follows on clicking on “OK.”

If we repeat these steps to relate the other two tables: the “Revenue” Table with “Expenses” table, then they also get connected in the data model as follows:

Excel now creates the relationship behind the scenes by combining data in the data model based on a common column: Salesperson ID (in this case).

Example #2

Now, in the above example, we wish to create a PivotTable that evaluates or analyzes the table objects:

1. We must first click on “Insert” -> “PivotTable.”

   
   

   

2. We need to click on the “Use an external data source” option in the resulting dialog box and click on “Choose Connection.”

   
   

   

3. Then, we must click on “Tables” in the resulting dialog box, select the “Workbook Data Model” containing three tables, and click “Open.”

   
   

   

4. Select the “New Worksheet” option in the location and click on “OK.”

   
   

   

5. Consequently, the “PivotTable Fields” pane will display table objects.

   
   

   
   
   
   
   
   
   Now we can make changes in the PivotTable accordingly to analyze the table objects as required.

6. For instance, in this case, if we wish to find the total revenue or revenue for a particular salesperson, then a PivotTable is created as follows:

   
   

   
   
   
   
   
   
   It is an immense help in the case of a model/table containing a large number of observations.

So, we can see that the pivot table instantly uses the data model (picking it by choosing connection) in Excel memory to show relationships between tables.

Things to Remember

• We can analyze data from several tables at once using the data model.
• By creating relationships with the data model, we surpass the need for using VLOOKUPUsing VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more, SUMIF, INDEX functionINDEX FunctionThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more, and MATCH formulas as we do not need to get all columns within a single table.
• Models are implicitly created when datasets are imported in Excel from outside sources.
• We can create table relationships automatically if we import related tables with primary and foreign key relationships.
• While creating relationships, the columns that we are connecting in tables should have the same data type.
• With the pivot tables created with the data model, we can add slicers and slice them on any field at pivot tables we want.
• The advantage of the Data Model over LOOKUP() functionsLOOKUP() FunctionsThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more is that it requires substantially less memory.
• Excel 2013 supports only one-to-one or one to many relationships, i.e., one of the tables must have no duplicate values on the column we are linking to.

Recommended Articles

This article is a guide to Data Model in Excel. We discuss creating a data model from Excel tables using connections and relationships, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

How do you query a data model in Excel?

How do I download data from data model in Excel?

How do I convert a data model to a table in Excel?

How do I extract data from Power Pivot data model?