Grouping items within a column

Hi Poohkrd, 

Thank you very much. I do not know the M code well at this point, so before I start figuring out how I would insert your M to the existing code, please tell me what is the reason of listing the "CC-002", etc. in the first string? There are millions of variations and, for example, all these CC-002, CC-00024, CP678, B89789 are only a minuscule fraction of the number of variations that are in my Card Number column. 
It is not possible to list them all one by one. 

I need a solution that somehow groups ALL CC-(numbers), and all three WebPay Id's, and then whatever else as Members. 

Best,

J

Hi Anonymous ,

You don't need to list any variation in the solution.  "CC-002" etc are listed in the first string is because it's automatically created string when you enter a table in Power BI.

What all you need to do is add a custom column in Power Query.

Enter the code in the dialog:

if Text.StartsWith( [Card number], "CC", Comparer.OrdinalIgnoreCase ) 
            then "Card" 
            else if Text.Contains( [Card number], "WebPay", Comparer.OrdinalIgnoreCase ) 
                then "WebPay" 
                else "Members"

Or if you prefer DAX, create a new column.

Column =
SWITCH (
    TRUE (),
    LEFT ( [Card number], 2 ) = "CC", "Card",
    CONTAINSSTRING ( [Card number], "Webpay" ), "WebPay",
    "Members"
)

Result:

I attach my sample below for your reference, you can download it to see the details.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous ,

It's my pleasure! Tweak the formula to:

if Text.StartsWith( [Card number], "CC", Comparer.OrdinalIgnoreCase ) 
            then "Card" 
            else if Text.Contains( [Card number], "WebPay", Comparer.OrdinalIgnoreCase ) 
                then "WebPay" 
                else if List.Contains({"Remote Start","Engineer","Blank"},[Card number]) 
                  then [Card number] else "Members"

Get the correct result:

Best Regards,
Community Support Team _ kalyj

Hi Anonymous ，

If the values are in italic in the right of the cell, I think it's because they are of number data type, try to click the left upper coner of the column and select "Text" before you add a custom column.

Best Regards,
Community Support Team _ kalyj