Consolidate/Condense Large Data Source

[brynjlai]

I am working with monthly files that each have about 3 million rows.  Each file is about 3 gb.  For different cloud resources, the files list the cost of the cloud resource by day.

What I would like to do is reduce the size of the data by condensing the file into monthly amounts rather than daily amounts.  I was hoping to import the large file into Power BI, condense the data (presumably by about a 30:1 ratio by reducing dates to months), export it, and then re-load it into a new Power BI report as a substantially smaller data source.  

My initial thought was to import the large data, create a table visualization, export the table, then use that table as the new data source.  But I'm encountering limits on how large a table I can export.

As an example, here is a before and after of what I'm trying to achieve.  One additional complication is there are other dimensions I'm trying to maintain.  I'm only trying to condense dates into months, while otherwise maintaining these other dimensions.

Thanks.

[danextian · Accepted solution]

Hi @brynjlai ,

You can use the group by feature in Power Query to summarize your data before loading it into the model. If you don't have a month and year columns yet you can add them first and then be as granular as needed in the group by dialogue box. Once the data is loaded,  you can then copy the table from the data view into an Excel file. Alternatively, you can use DAX Studio  to export if the file is too big to be just copy pasted (https://biinsight.com/exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-da...)

[adudani]

Hi @brynjlai ,

Unsure about how to go about the data export limit from a power bi table, other than Filtering it into smaller sample sizes (Monthly in this case would be the smallest grain).

However, A complementary approach might be reducing the size in Excel before importing it as well.

possible methods:

1. If all files are in the same folder, try get data -> from file -> folder. 

2. When query is expanded, group Amout by Month/ Month year.

3. Reduce text as much as possible. ( Could substitute Reservation as 0,1,2,3.. integers. and Virtual Machine with VM or ideally 1,2,3 ). 

4. Use power bi to get data from this excel file and try exporting monthly/ couple of months if possible.

Hope this helps.