Forum Discussion

Smash125's avatar
Smash125
New Member
3 years ago
Solved

ISNULL replicating in power bi

I am very new to power bi. I am trying to write ISNULL related query in Power BI

 

my SQL syntax

 

CASE WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName ELSE FirstName + ' ' + MiddleName + ' ' + LastName END AS FullName.

 

can anyone help me how to replicate in Power BI 

Reports
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, Smash125 ;

    In power query , you could add custom column.

    if [MiddleName]=null or  [MiddleName]=" " then [FirstName]&" "&[LastName]
else [FirstName]&" "&[MiddleName]&" "&[LastName]

    The final show:

    Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s0vSk/MU9JR8kpNzswrSQSylGJ1opWcgQxHIHZSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LastName = _t, FirstName = _t, MiddleName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastName", type text}, {"FirstName", type text}, {"MiddleName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Full Name", each if [MiddleName]=null or  [MiddleName]=" " then [FirstName]&" "&[LastName]
else [FirstName]&" "&[MiddleName]&" "&[LastName])
in
    #"Added Custom"


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    in M, Text.Combine will ignore any null

    NewStep=Table.CombineColumns(PreviousStepName,{"FirstName","MiddleName","LastName"},each Text.Combine(_," "),"FullName")

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Smash125 ;

    In power query , you could add custom column.

    if [MiddleName]=null or  [MiddleName]=" " then [FirstName]&" "&[LastName]
else [FirstName]&" "&[MiddleName]&" "&[LastName]

    The final show:

    Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s0vSk/MU9JR8kpNzswrSQSylGJ1opWcgQxHIHZSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LastName = _t, FirstName = _t, MiddleName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastName", type text}, {"FirstName", type text}, {"MiddleName", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Full Name", each if [MiddleName]=null or  [MiddleName]=" " then [FirstName]&" "&[LastName]
else [FirstName]&" "&[MiddleName]&" "&[LastName])
in
    #"Added Custom"


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.