Forum Discussion
Group Customer by Order type
Hi All,
I have a data set that includes customer ID, order number, order date, and order type. The order type has three possible values: free, paid, and mixed. For example, a single customer may place orders of all three types on different dates.
I want to analyze how many customers have placed orders in specific categories during a reporting period: only paid orders, only free orders, free and paid orders, and orders that include all three types. I attempted to achieve this using Power Query but got stuck along the way.
How do I solve this?
Sample data:
| Customer ID | Order No | Order Date | Order Type |
| CUST001 | ORD1001 | 1/11/2024 | Paid |
| CUST001 | ORD1002 | 10/11/2024 | Free |
| CUST001 | ORD1003 | 15/11/2024 | Mixed |
| CUST002 | ORD1004 | 3/11/2024 | Free |
| CUST002 | ORD1005 | 12/11/2024 | Paid |
| CUST003 | ORD1006 | 2/11/2024 | Mixed |
| CUST003 | ORD1007 | 8/11/2024 | Free |
| CUST003 | ORD1008 | 20/11/2024 | Paid |
| CUST004 | ORD1009 | 5/11/2024 | Paid |
| CUST004 | ORD1010 | 18/11/2024 | Mixed |
| CUST005 | ORD1011 | 7/11/2024 | Free |
| CUST005 | ORD1012 | 14/11/2024 | Paid |
| CUST006 | ORD1013 | 1/11/2024 | Mixed |
| CUST006 | ORD1014 | 11/11/2024 | Mixed |
| CUST007 | ORD1015 | 6/11/2024 | Free |
| CUST007 | ORD1016 | 19/11/2024 | Paid |
| CUST008 | ORD1017 | 4/11/2024 | Free |
| CUST008 | ORD1018 | 15/11/2024 | Paid |
| CUST009 | ORD1019 | 9/11/2024 | Mixed |
| CUST009 | ORD1020 | 21/11/2024 | Paid |
Expected data:
| Customer ID | Customer Type |
| CUST001 | All Three |
| CUST003 | All Three |
| CUST002 | Free and Paid |
| CUST005 | Free and Paid |
| CUST007 | Free and Paid |
| CUST008 | Free and Paid |
| CUST004 | Only Paid |
| CUST004 | Paid and Mixed |
| CUST009 | Paid and Mixed |
Hi TanzilHasan,
Thanks for reaching out Microsoft fabric community forum.
As per your query, follow the below steps to achieve you goal.I used the above data. I imported data into power query editor and in transform data i have done group by customer id and order type.
A separate table is created for each customer
Add custom column .use the below code in formula bar
Text.Combine(List.Distinct([Order Types][Order Type]), ", ")
Add another custom column by using the below code in formula bar:
if Text.Contains([Custom], "Paid") and Text.Contains([Custom], "Free") and Text.Contains([Custom], "Mixed") then "All Three"
else if Text.Contains([Custom], "Paid") and Text.Contains([Custom], "Free") then "Free and Paid"
else if Text.Contains([Custom], "Paid") and Text.Contains([Custom], "Mixed") then "Paid and Mixed"
else if Text.Contains([Custom], "Paid") then "Only Paid"
else if Text.Contains([Custom], "Free") then "Only Free"
else "Other"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you
5 Replies
hello TanzilHasan
please check if this accomodate your need.
create new table for summarize your original data.
Summarize =
SUMMARIZE(
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Customer ID],
"Type",
CONCATENATEX(
'Table',
'Table'[Order Type],
" ",
'Table'[Index],
DESC
)
),
"Customer Type",
IF(
[Type]="Mixed Free Paid",
"All Three",
SUBSTITUTE(
[Type],
" ",
" and "
)
)
),
'Table'[Customer ID],
[Customer Type]
)Hope this will help.
Thank you.
Create a Calculated Columns:
UniqueOrderTypes =
CONCATENATEX(
DISTINCT('Orders Table'[Order Type]),
'Orders Table'[Order Type],
", "
)CustomerType =
SWITCH(
TRUE(),
'Orders Table'[UniqueOrderTypes] = "Free, Paid, Mixed", "All Three",
'Orders Table'[UniqueOrderTypes] = "Free, Paid", "Free and Paid",
'Orders Table'[UniqueOrderTypes] = "Paid, Mixed", "Paid and Mixed",
'Orders Table'[UniqueOrderTypes] = "Free, Mixed", "Free and Mixed",
'Orders Table'[UniqueOrderTypes] = "Paid", "Only Paid",
'Orders Table'[UniqueOrderTypes] = "Free", "Only Free",
'Orders Table'[UniqueOrderTypes] = "Mixed", "Only Mixed",
"Other"
)💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedInThanks mate, this solution also works.
Hi TanzilHasan,
Thanks for reaching out Microsoft fabric community forum.
As per your query, follow the below steps to achieve you goal.I used the above data. I imported data into power query editor and in transform data i have done group by customer id and order type.
A separate table is created for each customer
Add custom column .use the below code in formula bar
Text.Combine(List.Distinct([Order Types][Order Type]), ", ")
Add another custom column by using the below code in formula bar:
if Text.Contains([Custom], "Paid") and Text.Contains([Custom], "Free") and Text.Contains([Custom], "Mixed") then "All Three"
else if Text.Contains([Custom], "Paid") and Text.Contains([Custom], "Free") then "Free and Paid"
else if Text.Contains([Custom], "Paid") and Text.Contains([Custom], "Mixed") then "Paid and Mixed"
else if Text.Contains([Custom], "Paid") then "Only Paid"
else if Text.Contains([Custom], "Free") then "Only Free"
else "Other"If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you
Thank you so much, this solution works.
