Reduce data rows

[Anonymous]

I've a table of purchased items stored in the Data Warehouse. The table has almost 20 million records. The table maintains daily transactions and hence the number if rows is huge.

I want to find a way where I can group the entries in the table where I can group items for each month for each customer. 

So transfer the example table below:

Date (DD/MM/YYYY)	Material #	QTY	group	Customer
1/2/2023	PN123	3	Stationary	A
1/2/2023	BK123	3	Stationary	B
2/2/2023	PN123	2	Stationary	A
3/2/2023	PN123	6	Stationary	B
1/3/2023	LT123	4	IT	A
1/3/2023	NB123	3	IT	B
2/3/2023	PN123	6	Stationary	A
3/3/2023	BK123	3	Stationary	B
4/3/2023	MS123	2	IT	B

To be similar to this one

Date	Material	Goup	Customer	QTY
Feb-23	PN123	Stationary	A	5
Feb-23	BK123	Stationary	B	3
Feb-23	PN123	Stationary	B	6

I want to do this as I load the data from the Data Warehouse

[marcelsmaglhaes]

Hey @Anonymous !

Can you write a SQL to extract the data? If yes, you can try use a group BY, or even create a view to reduce. 

If you can write a SQL, you can use the Group BY function on Power Query (Group By in Power BI using Power Query Editor - SqlSkull).

If this helps, please mark this question as solved to help people find quickly in our community!

Regards,
Marcel

[Anonymous]

Thanks. Yes, I can write SQL statement as I import the data from Oracle DB.

I'm not expert with SQL, so I was wondering how would grouping discard the data and just keep the month and year?

[marcelsmaglhaes · Accepted solution]

Hey @Anonymous !

You can write your SQL like below:

SELECT 

 EXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,

  EXTRACT(MONTH FROM  YOUR_DATE_FIELD) AS month,
    CUSTOMER, GROUP, MATERIAL,

  SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITY

FROM YOUR_TABLE

GROUP BY

  EXTRACT(YEAR FROM YOUR_DATE_FIELD),

  EXTRACT(MONTH FROM YOUR_DATE_FIELD),
    CUSTOMER,

   MATERIAL,

   GROUP

If this helps, please mark this question as solved to help people find quickly in our community!