create table based on another table

[Anonymous]

Hi all, I currently have a table (Table 1) that I want to use to create Table 2. For Table 2, I would like only one row for each customer ID, a column indicating whether the customer has the string "premium plus" in any of its SKUs, and the Max Quantity for that customer. Please see the example tables below, I would like to know how to use the summarize function or other functions to create Table 2. Thanks!

Table 1

Customer ID	SKU	Quantity
1	Premium Plus X	5
1	Regular	2
2	Regular	2
2	Regular	2
3	Premium Plus Y	5
3	Regular	4

Table 2

Customer ID	Has text "Premium Plus" in any SKU?	Max (Quantity)
1	Yes	5
2	No	2
3	Yes	5

[MikeJohnsonZA · Accepted solution]

Hi

To create a table ased on a single row per unique value we can use the SUMMARIZECOLUMNS funtion, we then nee to calculate the two values, this is faiully striagforward for the second on as it is simply the Maximum value for each row. The 'premium plus' is a little harder but can be done using the SEARCH function to finter for SKU's with the condition then counting such rows then converting those into YES or No. The below formula shoudl work for you to create the table you are looking for. 

Table2 =
SUMMARIZECOLUMNS (
    'Table1'[Customer ID],
    "Has text 'Premium Plus' in any SKU?",
        IF (
            COUNTROWS (
                FILTER ( 'Table1', SEARCH ( "Premium Plus", Table1[SKU], 1, 0 ) > 0 )
            ),
            "Yes",
            "No"
        ),
    "Max (Quantity)", MAX ( 'Table1'[Quantity] )
)