Forum Discussion
Group Customer by Order type
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
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 LinkedIn
Thanks mate, this solution also works.