Forum Discussion
Reduce data rows
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
Hey Anonymous !
You can write your SQL like below:
SELECTEXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,EXTRACT(MONTH FROM YOUR_DATE_FIELD) AS month,
CUSTOMER, GROUP, MATERIAL,SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITYFROM YOUR_TABLEGROUP BYEXTRACT(YEAR FROM YOUR_DATE_FIELD),EXTRACT(MONTH FROM YOUR_DATE_FIELD),
CUSTOMER,MATERIAL,GROUPIf this helps, please mark this question as solved to help people find quickly in our community!
3 Replies
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,
MarcelHey Anonymous !
You can write your SQL like below:
SELECTEXTRACT(YEAR FROM YOUR_DATE_FIELD) AS year,EXTRACT(MONTH FROM YOUR_DATE_FIELD) AS month,
CUSTOMER, GROUP, MATERIAL,SUM(CUSTOMER_QUANTITY) AS CUSTOMER_QUANTITYFROM YOUR_TABLEGROUP BYEXTRACT(YEAR FROM YOUR_DATE_FIELD),EXTRACT(MONTH FROM YOUR_DATE_FIELD),
CUSTOMER,MATERIAL,GROUPIf this helps, please mark this question as solved to help people find quickly in our community!