Text Combine Existing Column based on IF Statement from other columns

[sscanlon]

Hi, searched around and cannot find a way to do the following: 

I have a table that has been pieced together from Merging queries, my final step is to Text.Combine the word "Cloud" with a space to the beginning of ProductClass__c column IF the Tags_Cloud column is not null AND Manufacturer = "Microsoft"

I have the cobbled together PQ code below, I am sure I am doing something wrong? 

= Table.TransformColumnTypes(#"Added Custom",{{"Tags_Cloud", type text}}, each if [Tags_Cloud] <> null and [Manufacturer] = "Microsoft" then Text.Combine("Cloud", [ProductClass__c], " ") else Text.Combine("", [ProductClass__c], " "))

I get this error, not sure if I get this because I used Merged Queries to build this final Table? 

(This is a Salesforce Object connection, not sure if that matters)

Expression.Error: We cannot convert a value of type Function to type Text.
Details:
Value=[Function]
Type=[Type]

Thank you for any help!

[jennratten]

Hello!  You were on the right track.  Please give this a try.  Your post doesn't say what the value should be if the tags column is null or the manufacturer is not Microsoft, so I've just used a null in that scenario.

Table.AddColumn(#"Added Custom", "New Column Name", each if [Tags_Cloud] <> null and [Manufacturer] = "Microsoft" then "Cloud " & [ProductClass__c] else null, type text)

[sscanlon]

Thank you for the help and info, I am not wanting to add a column but to just add text to the beginning of the existing column [ProductClass__c]

My previous steps before this - in case it matters:

- Add a custom column "Tags_Cloud" and fill it with text from Tags_c column (this is just a snippet from that step.

= Table.AddColumn(#"Replaced Value1", "Tags_Cloud", each if [Tags__c] = null then null else "NONE")

- Then I changed the column type to text - and that is the step here that we are trying to fix.

I tried to take your query and change the beginning back to my TransformColumnTypes

= Table.TransformColumnTypes(#"Added Custom", "Tags_Cloud", each if [Tags_Cloud] <> null and [Manufacturer] = "Microsoft" then "Cloud " & [ProductClass__c] else null, type text)

But I get this error: 

Expression.Error: 4 arguments were passed to a function which expects between 2 and 3.
Details:

Thanks again for your help!
Pattern=
Arguments=[List]

[jennratten]

Okay, that helps a lot.  Change your add column step to this (adds the column and makes it text in one step):

= Table.AddColumn(#"Replaced Value1", "Tags_Cloud", each if [Tags__c] = null then null else "NONE", type text)

Make this your next step:

= Table.ReplaceValue(#"Added Custom", each [Tags_Cloud], each if [Tags_Cloud] <> null and [Manufacturer] = "Microsoft" then "Cloud " & [ProductClass__c] else null, Replacer.ReplaceValue, {"Tags_Cloud"})

[sscanlon]

Perfect!! I would have never figured that out. Thank you so much.

One last question - for the "else null" part, is there a way to say "just skip it" instead of inserting a null value? I have other values that I need to keep if condition is not met. 

Thanks again!

[sscanlon]

Ah I think its the "Replacer.ReplaceValue" that is replacing everyhing in the field. 

Maybe a Text.Combine instead? 

As you can tell I am VERY new to Power Query.

[jennratten]

You are on the right track again!  Replacer.ReplaceValue is the replacer function.  The replacer function tells Power Query how to perform replacements.  Replacer.ReplaceValue means replace the contents of the entire cell.  To replace subtrings you would use Replacer.ReplaceText.  If you'd like no action to be taken if the criteria is not satisfied then you would refer to the original text [Tags_Cloud].

So...

= Table.ReplaceValue(#"Added Custom", each [Tags_Cloud], each if [Tags_Cloud] <> null and [Manufacturer] = "Microsoft" then "Cloud " & [ProductClass__c] else [Tags_Cloud], Replacer.ReplaceValue, {"Tags_Cloud"})

[jennratten · Accepted solution]

You are on the right track again!  Replacer.ReplaceValue is the replacer function.  The replacer function tells Power Query how to perform replacements.  Replacer.ReplaceValue means replace the contents of the entire cell.  To replace subtrings you would use Replacer.ReplaceText.  If you'd like no action to be taken if the criteria is not satisfied then you would refer to the original text [Tags_Cloud].

So...

= Table.ReplaceValue(#"Added Custom", each [Tags_Cloud], each if [Tags_Cloud] <> null and [Manufacturer] = "Microsoft" then "Cloud " & [ProductClass__c] else [Tags_Cloud], Replacer.ReplaceValue, {"Tags_Cloud"})

[sscanlon]

Perfect, thank you again, just tested and working great, I really appreciate the fast replies and help!

[jennratten]

You are very welcome!