cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
narchans4u Frequent Visitor
Frequent Visitor

aggregate the data to the month/quarter level

Hi,

I have a dataset which has rows at day level(one row for one day) - and thus my power bi workbook is of ~1GB (i have imported data from DB). Is it possible to aggregate the data to the month/quarter level so that I can reduce my the size of the power bi workbook? With tableau background - when I try to extract data of tableau workbook i have an option to define the granularity - by aggregating the data for visible dimensions(roll up dates to: year/qtr/month). Is there any option in PBI similar to this?

Much thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
dearwatson Member
Member

Re: aggregate the data to the month/quarter level

The Query editor has a Group By function to roll up rows to other levels of granularity if you need it...

 

I would add a new column which gets the start of the month (New Column -> Date-> Start of Month) then group by that column as required.

 

I think that should do it.


Cheers

Greg

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
5 REPLIES 5
narchans4u Frequent Visitor
Frequent Visitor

Is it possible to aggregate the data to the required granularity?

Hi,

 

I have a dataset which has rows at day level(one row for one day) - and thus my power bi workbook is of ~1GB. Is it possible to aggregate the data to the month/quarter level so that I can reduce my the size of the power bi workbook?

With tableau background - when I try to extract data of tableau workbook i have an option to define the granularity - by aggregating the data for visible dimensions(roll up dates to: year/qtr/month).

Is there any option in PBI similar to this?

Much thanks.

 

dearwatson Member
Member

Re: aggregate the data to the month/quarter level

The Query editor has a Group By function to roll up rows to other levels of granularity if you need it...

 

I would add a new column which gets the start of the month (New Column -> Date-> Start of Month) then group by that column as required.

 

I think that should do it.


Cheers

Greg

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
narchans4u Frequent Visitor
Frequent Visitor

Re: aggregate the data to the month/quarter level

Thanks for your reply. Is this going to reduce the over all size of the PBI workbook? I am trying hands on now.

narchans4u Frequent Visitor
Frequent Visitor

Re: aggregate the data to the month/quarter level

This worked. Thank you.

bahar1 Frequent Visitor
Frequent Visitor

Re: aggregate the data to the month/quarter level

Hi guys,

 

Quick question. Can you let me know how I can use the same method if I have different years? 

If I group by on month level I use the year information. Is there any way to group by on Month&Year level.

 

many thanks,

Bahar

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 2,620 guests
Please welcome our newest community members: