Power Query Replace Text if it Contains A Certain Word or Group of Words

bbalch

Board Regular
Joined
Feb 23, 2015
Messages
59
Hello everyone and thanks in advance for your help.

I have a query that pulls in the banking activity each month and compares it to the prior months' activity using a pivot table. This works well as long as the items are named consistently at the bank. There are a few items where each transaction is assigned a name and a new number each month and therefore can't be compared in the pivot table. The query pulls data from a CSV file and I'm looking at the "Description" column.

Examples where the data is included in each description and therefore a pivot table doesn't recognize these as similar items.

TRANSFER FROM ACCOUNT 1.010318
TRANSFER FROM ACCOUNT 1.010218
PRINCIPAL 4-401032018
PRINCIPAL 4-401022018


What I would like to do is modify my query so that it looks to the Description column for "TRANSFER FROM ACCOUNT 1....." and renames all items "TRANSFER FROM ACCOUNT 1" and "PRINCIPAL 4-......" and renames these items to "PRINCIPAL". I've tried using Transform > Replace Values and it produced this code, however, this does not work. Any suggestions on how to replace the values in the Description column if the contain a certain word or group of words?

Code:
 #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","PRINCIPAL 4-*","PRINCIAPL",Replacer.ReplaceText,{"Description"})

Code:
 #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","TRANSFER FROM ACCOUNT 1*","TRANSFER FROM ACCOUNT 1",Replacer.ReplaceText,{"Description"})
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top