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. Show 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:
Explanation:
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:
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:
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:
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.
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 contentsExplanation
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 ExamplesYou can download this Data Model Excel Template here – Example #1We 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:
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:
Adding the “Table” objects to the Data Model: Via Connections or Relationships. Via Connections
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:
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 #2Now, in the above example, we wish to create a PivotTable that evaluates or analyzes the table objects:
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
Recommended ArticlesThis 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?In Excel, you may want to load a query into another worksheet or Data Model.. In Excel, select Data > Queries & Connections, and then select the Queries tab.. In the list of queries, locate the query, right click the query, and then select Load To. ... . Decide how you want to import the data, and then select OK.. How do I download data from data model in Excel?If by Power Query, right click on query name in right pane and Load to table. After that Data->Existing connections, select this dummy table and load it as table into the sheet. Here keep command type Table and type the name of the table in data model if you'd like to download it as it is.
How do I convert a data model to a table in Excel?Try it!. Select a cell within your data.. Select Home > Format as Table.. Choose a style for your table.. In the Format as Table dialog box, set your cell range.. Mark if your table has headers.. Select OK.. How do I extract data from Power Pivot data model?Get data using the Power Pivot add-in. Click Home > From Data Service if the data is from Microsoft Azure Marketplace or an OData data feed.. Click Home > Get External Data > From Other Sources to choose from the entire list of data sources.. |