Aggregate daily sales data to monthly

[Anonymous]

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.

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[sokg · Accepted solution]

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

Then click ok.

Is this what you want???

[sokg]

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]

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.