Understanding the Excel Data Model

[DashboardNovice]

Hello,

I have a table with over 76,000 rows. When I make a selection from an AutoFilter it takes for ever to process. I thought I might have read somewhere that when you put the tables into the Data Model or into PowerQuery or PowerPivot, that the data is not really in Excel and therefore data will process faster. Is this true?

1. If I save a table into the Data Model, would I then be able to delete it from the worksheet tab, then do any data clean up in the PowerQuery Editor?
2. If I am in the PowerQuery Editor, can you still do VLOOKUPs there?
3. Can anybody provide any links and/or videos with how PowerQuery, PowerPivot, Connections and the Data Model tie in to each other?

 

[Chihiro]

Is this true?

Depends on your data structure, and what transformation steps need to be applied.

Say, for example, you have 76k rows, with 10 columns. This will load much slower in PQ/Data model than having 760k rows with single column etc.

Vertipaq engine, as name suggest, is meant for vertical compression, so having more rows rather than columns is desirable (i.e. flat table structure as opposed to cross tab structure).

But in general, loading to PQ/Data model is quick way to transform your data. PQ is the ETL tool, and Data Model/PowerPivot is analysis tool.

1. You should do all data transformation within PowerQuery. Not in data model. Data model is where you will be setting up DAX calculations for analysis/aggregation of data. There are certain exceptions (dynamic grouping by ranked measure etc), but in general, this holds true.

2. VLOOKUP as function isn't available. You'd use either Joins or other operation to transform data within PQ. Or build relationships within Data model.

3.

 

[DashboardNovice]

This is helpful. Thank you.