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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
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)
