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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Aggregate daily sales data to monthly

I got daily sales data from the last three years (1M+ rows) in total. Through Power Querry I have joined/appended all the data so it's ordered by date. However, I am unable to convert daily sales data into monthly totals. I want to aggregate the data so that it occupies much less rows (for performance). 

 

I already tried Groupping and Pivot  Table options, but no success. Also I do not considered them as an option as I need to automate it. 

 

The idea is to have AGGREGATED monthly data every month and copy/paste in a database (less space and faster performance). 

 

I am sure there is some option for it. I tried Pivot and Unpivot...but I did not get it. Please I need suggestion/advice.

 

Thanks for your support.

SalesData.PNG

 

 

1 ACCEPTED SOLUTION
sokg
Solution Supplier
Solution Supplier

1. Import your files from folder to Power query

2. Create a Revenue Column (which is qty * price)

3. Create a Year column (from Date)

4. Create a Month column (from Date)

5. Merge Year - Month to a new column (Year - Month)

6. Right click on Year - Month column and choose Group by..

 

Capture.JPG

 

Then click ok.

 

Capture1.JPG

 

Is this what you want???

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sokg
Solution Supplier
Solution Supplier

1. Import your files from folder to Power query

2. Create a Revenue Column (which is qty * price)

3. Create a Year column (from Date)

4. Create a Month column (from Date)

5. Merge Year - Month to a new column (Year - Month)

6. Right click on Year - Month column and choose Group by..

 

Capture.JPG

 

Then click ok.

 

Capture1.JPG

 

Is this what you want???

sokg
Solution Supplier
Solution Supplier

Do you get this data from excel???

How offen do you get this data??? (daily, monthly???)

Do you want to import these data to Power bi??

Anonymous
Not applicable

I get them to my email an excel file attached. All the Excel files have same format.

I get them every day at morning time.

I would like more to aggregate data then take it to Excel (throught BI or Power Pivot) to create a separate aggregate Database.

 

For now I am using Connection to folder and loaded data in Pivot  Table. But due to hige number of files, it becomes very slow when updating with new daily files. So my idea was to have monthly files instead of daily.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.