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

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 @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!

Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to give Kudos and mark my post as a solution!
Imagem de fundo



View solution in original post

3 REPLIES 3
marcelsmaglhaes
Super User
Super User

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


Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to give Kudos and mark my post as a solution!
Imagem de fundo



Anonymous
Not applicable

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 @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!

Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI

If I've helped, don't forget to give Kudos and mark my post as a solution!
Imagem de fundo



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.