Log In
Register
Home
Active Member
April 21, 2021
Offline
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" (all three of them) the Customer Name will be NYC -- if desc contains "IL" Chicago" "O'hare" (again all 3 of them) the customer name will be "Chicago City" . Thanks
November 8, 2013
Offline
Hi Furqan,
You can add the new column with this formula:
if List.ContainsAll(Text.Split([Description]," "), {"New York", "Manhattan", "Times Sq"}) then "NYC" else if List.ContainsAll(Text.Split([Description]," "), {"IL", "O'hare", "Chicago"}) then "Chicago City" else null
Active Member
April 21, 2021
Offline
November 8, 2013
Offline
Use a lookup table then, with 2 columns:
"City" Column 1 should have the city name, "Lookup" Column 2 should have a space separated text string with the matches: "New York Manhattan Times Sq" (only space separated, no other separator.)
Create this function with the name SearchText:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Lookup", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.ContainsAll(Text.Split(TextString," "), Text.Split([Lookup]," ")) then [City] else null),
#"Filtered Rows" = try Text.Combine(Table.SelectRows(#"Added Custom", each ([Custom] <> null))[Custom],", ") otherwise null
in
#"Filtered Rows"
You can use this function in your main query by adding a new column, calling this function:
= Table.AddColumn(Source, "Custom", each SearchText([Description]))
Note that a string like:"New Sq Times Manhattan York" will also return "New York", each space separates the match string into separate words, the formula will actually look after 5 words matches, not 3.
These posts might also help:
https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query
1 Guest(s)
