Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
2 years ago
Solved

Concatenate dealing with nulls

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

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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
  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

    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