Power Query
Power Pivot
Power BI
December 17, 2016
I have 6 Power Pivot tables in different files that I created by transforming and joining a number of odata tables and adding them to the data model. Is there a way to grab the totals from these files using a more elegant solution than using the consolidate function?
Thanks in advance.
Power Query
Power Pivot
Power BI
December 17, 2016
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?
July 16, 2010
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
Power Query
Power Pivot
Power BI
December 17, 2016
Power Query
Power Pivot
Power BI
December 17, 2016
July 16, 2010
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
1 Guest(s)