POWER QUERY: Add value in column based on search in another column

[furqan_yousuf]

I want this done in power query: I downloaded statement from bank and based on certain value in the description field I want to assign value to it. For eg. I download transactions from bank and it has 3 columns (date, description, amount) I will add 4 the column next to amount called "Customer Name". If the description contains "New York" "Manhattan" "Times Sq" the Customer Name will be NYC -- if desc contains "IL" Chicago" "O'hare" the customer name will be "Chicago City" . Excuse my english

 

[smozgur]

I am sure there are many other ways, probably better ones, but here is what I came up with:

Sample source data:

Book1
	A	B	C
1	Date	Description	Amount
2	1-Jan	New York	100
3	4-Jan	IL	123
4	7-Jan	New York	150
5	9-Jan	Chicago	199
6	22-Jan	San Diego	330
Sheet1

let
    // Sample source data taken from the worksheet
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Data = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Description", type text}, {"Amount", Int64.Type}}),

    // The first list item is "Customer Name" return value for the match
    filter = {{"NYC", "New York", "Manhattan", "Times Sq"},
              {"Chicago City", "IL", "Chicago", "O'hare"}},


    Filter = Table.AddColumn(Data, "Customer Name",  
        (d) => List.Generate(
            () => [i = 0],
            each [i] < List.Count(filter),
            each [i = [i] + 1],
            each if List.AnyTrue(List.Transform(filter{[i]}, each Text.Contains(d[Description], _)))
                    then filter{[i]}{0}
                    else null
        )
    ),
    Result = Table.TransformColumns(Filter, {"Customer Name", each Text.Combine(List.Transform(_, Text.From),"-"), type text})

in
    Result

Book1
	A	B	C	D
1	Date	Description	Amount	Customer Name
2	1/1/2021	New York	100	NYC
3	1/4/2021	IL	123	Chicago City
4	1/7/2021	New York	150	NYC
5	1/9/2021	Chicago	199	Chicago City
6	1/22/2021	San Diego	330
Result