Conditional Column with Replace

[CherC]

Trying to get the replace function to work when adding a conditional column.  Pls see screen shot below.  What I need is:

Title: Region     Column Name: Team     Operator: Begins with     Value: GSA-     Output: replace("GSA-","").  It's this last section of "Output" that I can't get to work.  How do I put a replace feature here?  It can't be done any other way because I have 5 clauses.  Any ideas?

[Jimmy801 · Accepted solution]

Hello @CherC 

add a custom column (not conditional column) and put this formula

if Text.StartsWith([Team],"GSA") then Text.Replace([Team],"GSA",",") else [Team]

Here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg921E1MSk5RitWBcAyNjMHsUA8PXVNTM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Region", each if Text.StartsWith([Team],"GSA") then Text.Replace([Team],"GSA",",") else [Team])
in
    #"Added Custom"

transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[KiwiPete · Accepted solution]

The following should work

Basically it starts with the table to change, creates a second table with two columns, the strings to find and the the strings to replace with.

Then uses a List.Accumulate which loops through each record of the original table a number of times for each find/replace combination from the second table.

Chandoo has a great explanation here 

https://chandoo.org/wp/multiple-find-replace-list-accumulate/ 

Note the sequence of the "GSA-US-" and the "GSA-US" is very significant. 

"GSA-US" needs to go second. 

This code will also work if there are multiple find/replaces in each record.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg921HUMcHTWTa1IzC3ISTVUitWBiLr6ujrCRI3goqHBMDFjJDGokAlWzaZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t]), ReplaceTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JL03RVdJRUorViVZyD3bUdQxwdEYRcPV1dUQRCA1G4wJ5FRUVGBr8nIMdlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Accumulate(List.Numbers(0,Table.RowCount(ReplaceTable)-1),[Team], (state,current)=>Text.Replace(state,ReplaceTable[Find]{current},ReplaceTable[Replace]{current}))) in #"Added Custom"

Please mark as resolved if this works. Any Kudos's appreciated.

[Jimmy801 · Accepted solution]

Hello @CherC 

add a custom column (not conditional column) and put this formula

if Text.StartsWith([Team],"GSA") then Text.Replace([Team],"GSA",",") else [Team]

Here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg921E1MSk5RitWBcAyNjMHsUA8PXVNTM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Region", each if Text.StartsWith([Team],"GSA") then Text.Replace([Team],"GSA",",") else [Team])
in
    #"Added Custom"

transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[CherC]

Hi Jimmy, I thank you so much for responding so quickly.  Please ignore my earlier post and take a look at the examples below.  In the first example, it only executed the first 'if' and ignored the others.  Next I tried combining some of the statements using 'or' but that gave me an error.  Your help would be greatly appreciated.

1)  if Text.StartsWith([OwnerWorkGroup],"Cloud-") then Text.Replace([OwnerWorkGroup],"Cloud-","") else if Text.StartsWith([OwnerWorkGroup],"GSA-APAC-") then Text.Replace([OwnerWorkGroup],"GSA-APAC-","") else if Text.StartsWith([OwnerWorkGroup],"GSA-EMEA-") then Text.Replace([OwnerWorkGroup],"GSA-EMEA-","") else if Text.StartsWith([OwnerWorkGroup],"GSA-US-") then Text.Replace([OwnerWorkGroup],"GSA-US-","") else if Text.StartsWith([OwnerWorkGroup],"GSA-US") then Text.Replace([OwnerWorkGroup],"GSA-EMEA-","NCSA") else [OwnerWorkGroup]

2) if Text.StartsWith([OwnerWorkGroup] = "Cloud-") or Text.StartsWith([OwnerWorkGroup] = "GSA-APAC-") or Text.StartsWith([OwnerWorkGroup] = "GSA-EMEA-") or Text.StartsWith([OwnerWorkGroup] = "GSA-US-") then Text.Replace([OwnerWorkGroup],”Cloud-“ or “GSA-APAC-“ or “GSA-EMEA-“ or “GSA-US-“,””) else if Text.StartsWith([OwnerWorkGroup],”GSA-US”,”NCSA”) else [OwnerWorkGroup]

[Jimmy801]

Hello @CherC 

I cannot follow you. In your first example you refer only to one column thats the ownerworkgroup. Why only the first step is executed? Whenever it starts with GSA-APAC then the second step should be executed. I cannot see any problems with your first code. Be aware that Power query is case sensitive. If you can show me a screenshot where the first code isn't working I can say maybe a little more.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[CherC · Accepted solution]

Hi Jimmy, I don't know what happened yesterday but it seems to be working today.  Gremlins!  Thank you so much for your help!  You're the Best!

[KiwiPete · Accepted solution]

The following should work

Basically it starts with the table to change, creates a second table with two columns, the strings to find and the the strings to replace with.

Then uses a List.Accumulate which loops through each record of the original table a number of times for each find/replace combination from the second table.

Chandoo has a great explanation here 

https://chandoo.org/wp/multiple-find-replace-list-accumulate/ 

Note the sequence of the "GSA-US-" and the "GSA-US" is very significant. 

"GSA-US" needs to go second. 

This code will also work if there are multiple find/replaces in each record.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg921HUMcHTWTa1IzC3ISTVUitWBiLr6ujrCRI3goqHBMDFjJDGokAlWzaZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t]), ReplaceTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JL03RVdJRUorViVZyD3bUdQxwdEYRcPV1dUQRCA1G4wJ5FRUVGBr8nIMdlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Find = _t, Replace = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Accumulate(List.Numbers(0,Table.RowCount(ReplaceTable)-1),[Team], (state,current)=>Text.Replace(state,ReplaceTable[Find]{current},ReplaceTable[Replace]{current}))) in #"Added Custom"

Please mark as resolved if this works. Any Kudos's appreciated.