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
Solved! Go to Solution.
Hey @YJAMOUS !
You can write your SQL like below:
SELECT
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
User | Count |
---|---|
123 | |
63 | |
56 | |
47 | |
42 |
User | Count |
---|---|
113 | |
65 | |
61 | |
56 | |
45 |