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?
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"})
