Group Customer by Order type

[TanzilHasan]

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

[Anonymous · Accepted solution]

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 

[TanzilHasan]

Thank you so much, this solution works. 

[Kedar_Pande]

@TanzilHasan 

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

[TanzilHasan]

Thanks mate, this solution also works. 

[Irwan]

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.