Hi Adele,

Do any of the workbooks contain all of the odata tables? I'm just wondering if you can create a PivotTable to return the totals that you want, rather than referencing totals in PivotTables in other workbooks.

Mynda

Unfortunately not. I'm reporting on 6 different internal communication channels. To create the tables for the visualizations I need to join at least 5 different odata tables for each channel. I create using connection only and then load the final joined table into the data model. Putting all of the tables in one workbook really slows things down.

Question about PowerBI can I load more than one data model? I wanted to create a dashboard that connected to all of my workbooks but when I try to add another spreadsheet my other model seems to disappear. If you can only add one data model I may have to create a 'super' workbook that includes all of my tables and queries.

Also wondering what the limitation is on rows in PowerQuery. I'm trying to access a table in odata and told the reason it won't update is because of the amount of rows. I know last month I had 750K rows and it wasn't a problem and this month is just over a million (or so I am told, I can't even count the rows without getting an error message.) I suspect there is something wrong with the table but keep being told it's a PowerQuery issue. Any ideas?

Hi Adele,

Power BI can only have one model, just like Excel can only have one Power Pivot model per file. Power Query has no limit on the number of rows. Power Query doesn't actually bring the data into itself, it simply shows you a preview/sample of your data. If you try to perform functions that require all of the data then you may run into problems. For example, you might be better off puting the data into Power Pivot and let it do the row count in a PivotTable.

Is it possible to create the 5 odata tables that combine the data from the 6 different channels? You could use Power Query to get the channel data and then merge it into your 5 different tables so you can load it into one Power Pivot model, be it in Excel or Power BI.

Mynda

That was my next plan of attack. Having problems with directly accessing an odata table in PowerPivot but that may be a firewall or something on our corporate IT side.

Thanks

AJB

If I build and join my 20 tables into 5 different queries in one spreadsheet in PowerQuery then add to my data model in PowerPivot will I be able to consolidate the totals from the different pivot tables?

Hi Adele,

If these 5 queries all contain Fact type data, which it sounds like they do, then they really should be consolidated into one table before loading into Power Pivot. That way you can query that single table in a PivotTable to get the totals you want.

You can improve the efficiency of the model by creating a star schema layout for dimension type data so that your fact table doesn't contain loads of duplicate information. Refer to sessions 6.07 and 6.08 of the Power Pivot course for more information on the ideal data structure and optimizing your model.

Let me know if you get stuck.

Mynda