Concatenate dealing with nulls

[cottrera]

I have the following m-code

= [Work Stream] & " - " & [Trade] & " - " & [Job Description] & " - " & [Supplier Name] & " - " & [Job Details] & " - " &
Number.ToText([Amount ex VAT])

The issue is that some of the fields called Amount ex VAT contains null values. The output of the code above seems to be affected by the null values.

I require help to amend this code to ignore if null and include the amount in the concatenation if <> null.  I tried asking CoPilot and it kept giving me this code. However I am getting an error and it highlights if as the error

[Work Stream] & " - " & [Trade] & " - " & [Job Description] & " - " & [Supplier Name] & " - " & [Job Details] & " - " &
if [Amount ex VAT] <> null then Number.ToText([Amount ex VAT]) else ""

thanks

Richard

[cottrera]

thank you foryour quick reponse the code worked

[tharunkumarRTK]

@cottrera 

Try this syntaxm it should work

[Work Stream] & " - " & [Trade] & " - " & [Job Description] & " - " & [Supplier Name] & " - " & [Job Details] & " - " &
(if [Amount ex VAT] <> null then Number.ToText([Amount ex VAT]) else "")

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[cottrera]

Thank you , your code also works

[dufoq3 · Accepted solution]

Hi @cottrera, provide sample data and expected result please, but maybe this is what are you looking for:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClfSUQoBYi8XIBEMYjgBCUOlWB2gnBFI0ggsCyKDwUwnEAmRNwbJG4PlQWQwmOkEIo2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Stream" = _t, Trade = _t, #"Job Description" = _t, #"Supplier Name" = _t, #"Job Details" = _t, #"Amount ex VAT" = _t]),
    ReplacedValueBlankToNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Amount ex VAT"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValueBlankToNull,{{"Amount ex VAT", type number}}),
    Ad_Concat = Table.AddColumn(ChangedType, "Concat", each Text.Combine({ [Work Stream], [Trade], [Job Description], [Supplier Name], [Job Details], Text.From([Amount ex VAT]) }, " - "), type text)
in
    Ad_Concat