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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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