cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YJAMOUS
Helper IV
Helper IV

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 #QTYgroupCustomer
1/2/2023PN1233StationaryA
1/2/2023BK1233StationaryB
2/2/2023PN1232StationaryA
3/2/2023PN1236StationaryB
1/3/2023LT1234ITA
1/3/2023NB1233ITB
2/3/2023PN1236StationaryA
3/3/2023BK1233StationaryB
4/3/2023MS1232ITB

 

To be similar to this one

 

DateMaterialGoupCustomerQTY
Feb-23PN123StationaryA5
Feb-23BK123StationaryB3
Feb-23PN123StationaryB6

 

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

1 ACCEPTED SOLUTION

Hey @YJAMOUS !

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!

View solution in original post

3 REPLIES 3
marcelsmaglhaes
Responsive Resident
Responsive Resident

Hey @YJAMOUS !

 

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

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?

 

Hey @YJAMOUS !

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!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors