Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
brynjlai
New Member

Consolidate/Condense Large Data Source

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.

 

brynjlai_1-1672795488889.png

 

Thanks.

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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...)

danextian_0-1672797156873.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

2 REPLIES 2
danextian
Super User
Super User

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...)

danextian_0-1672797156873.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
adudani
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.