Power Query New Column Concatenate Problem

[gheyman]

I created a new column and contatenated these values without any problems.

= Table.AddColumn(#"Added Custom2", "Detail Comments", each "{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}" & " " & "{Tariff: $" & Number.ToText([#"Tariff Cost $"]) & "}" & " " & "{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}")

{Excess Cost: $500.25} {Tariff: $205.78} {Excess Cost: $500.25}

But I wanted to add a Comment column (Text) to it. Once I do this the new column is blank. can someone explain what I am doing wrong?

[Qty Comment] &"{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}" & " " & "{Tariff: $" & Number.ToText([#"Tariff Cost $"]) & "}" & " " & "{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}"

The Formula does not tell me I have a syntax error so I dont understand why this does not work. it comes up null.

Thanks for the help.

 

[JGordon11]

Try it like this:

each let QC = [Qty Comment] in (if QC=null then "" else QC) &"{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}" & " " & "{Tariff: $" & Number.ToText([#"Tariff Cost $"]) & "}" & " " & "{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}"

Joining a null with anything will return null, so the code above changes the null to zero length text. If any of the other columns you're concatenating might contain a null you will need to do something similar with them.

If the embedded "let ... in" bothers you, this will do the same thing a little less efficiently:

each (if [Qty Comment] =null then "" else [Qty Comment]) &"{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}" & " " & "{Tariff: $" & Number.ToText([#"Tariff Cost $"]) & "}" & " " & "{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}"

 

[gheyman]

That was the problem (null). Thank you!

 

[RoryA]

If your version supports it, you can also use the coalesce operator:

([Qty Comment]??"") &"{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}" & " " & "{Tariff: $" & Number.ToText([#"Tariff Cost $"]) & "}" & " " & "{Excess Cost: $" & Number.ToText([#"Excess Material Cost $"]) & "}"

or use Text.Combine and pass the sections as a List.