Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
cottrera
Post Prodigy
Post Prodigy

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

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

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

 

Result

dufoq3_0-1709196342771.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
cottrera
Post Prodigy
Post Prodigy

thank you foryour quick reponse the code worked

tharunkumarRTK
Solution Sage
Solution Sage

@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

 

Thank you , your code also works

dufoq3
Super User
Super User

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

 

Result

dufoq3_0-1709196342771.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors