CODEWITHSUNDEEP
excelpowerbipowerquery
user8200199
user8200199

Reputation:

Power Query Change Text if it Contains 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 to 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 they contain a certain word or group of words?

#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","PRINCIPAL 4-*","PRINCIAPL",Replacer.ReplaceText,{"Description"})
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","TRANSFER FROM ACCOUNT 1*","TRANSFER FROM ACCOUNT 1",Replacer.ReplaceText,{"Description"})

Upvotes: 0

Views: 14857

Answers (2)

Mike Honey
Mike Honey

Reputation: 15017

I'm more of a UI guy, so I would use the Query Editor ribbon and go to Add Column / Conditional Column. In that dialog I would specify the series of rules to output what you want, along the lines of:

If [Description] contains TRANSFER FROM ACCOUNT 1 then PRINCIPAL
Else If [Description] contains PRINCIPAL 4- then PRINCIPAL
Else [Description]

This will produce an added column (it generates a Table.AddColumn), so I would then add Steps to remove the original [Description] column and rename the added column to suit.

Upvotes: 1

Ivan Bond
Ivan Bond

Reputation: 61

Try to do following:

Define a function (logic can be more complex)

fGetNewDescription = (Description as text) =>
let    
    #"Fist Part" = Text.Start( Description , 23 ),
    #"Second Part" = Text.Range( Description , 31, 11 ),
    #"New Description" = #"Fist Part" & " " & #"Second Part"
in

Call this function when add new column

= Table.AddColumn(#"Changed Type", "New Description", each 
    if Text.Contains( [Description], 
                      "TRANSFER FROM ACCOUNT " ) and 
       Text.Contains( [Description], 
                      "PRINCIPAL " ) 
    then fGetNewDescription([Description]) 
    else [Description] )

Or if you want to transform column in-place

= Table.TransformColumns( #"Changed Type", { {"Description", 
    each if Text.Contains( _, "TRANSFER FROM ACCOUNT " ) and 
            Text.Contains( _, "PRINCIPAL " ) 
         then fGetNewDescription( _ ) 
         else _, type text}})

Probably you have other descriptions, so I decided to add check if it contains both "TRANSFER FROM ACCOUNT " and "PRINCIPAL ".

Upvotes: 2

Related Questions