Forum Discussion

RahulPBI's avatar
RahulPBI
Helper I
6 years ago
Solved

Conditional Column to check If Column Blank or null then append or use non blank column

Hi Expert 

I have three columns some of the columns have data, some have blanks and some are null

What I am trying to do is create a conditional column in power query which will check column1, column2 & column3 and merge the data into another column

It needs to ignore the blanks and null values and if two columns have data it merges both the data with a space in between

 

See below screen shots of the data and the expected result. I have a sample pbix file but not sure how to upload it here

 

Thanks

 

 

Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    RahulPBI ,

     

    Try this m code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDJCsMgEIbfJeccGpfYHs2maEuL0UMJeYOQW9+/IyodpDjgzPfPxmxbQy+i46xpm2h7mwGvQC9qcIPoyQg8cKQchnFMIhediOL5OY5Skf38rdOyLtN/wTmFBbBgrTUm0URm72atEdDq8Q4lloQyRkvTDDVlvO8raCGT3CuIN/pNUGpWymMwLd7jHbwxIRhTiI+9KUMnE9d4lQCLUI4H5IEvyM75+xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref1 = _t, Ref2 = _t, Ref3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref1", type text}, {"Ref2", type text}, {"Ref3", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Ref1", "Ref2", "Ref3"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","null",null,Replacer.ReplaceValue,{"Ref1", "Ref2", "Ref3"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each let
    _1 = if [Ref1] <> null then [Ref1] else "",
    _2 = if [Ref2] <> null then [Ref2] else "",
    _3 = if [Ref3] <> null then [Ref3] else "" in
    _1 & " " & _2 & " " & _3),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Trim, type text}})
    in
    #"Trimmed Text"

     

4 Replies

    • RahulPBI's avatar
      RahulPBI
      Helper I
      6 years ago

      Thanks Ricardo

      Hope the below helps

       

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDJCsMgEIbfJeccGpfYHs2maEuL0UMJeYOQW9+/IyodpDjgzPfPxmxbQy+i46xpm2h7mwGvQC9qcIPoyQg8cKQchnFMIhediOL5OY5Skf38rdOyLtN/wTmFBbBgrTUm0URm72atEdDq8Q4lloQyRkvTDDVlvO8raCGT3CuIN/pNUGpWymMwLd7jHbwxIRhTiI+9KUMnE9d4lQCLUI4H5IEvyM75+xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref1 = _t, Ref2 = _t, Ref3 = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref1", type text}, {"Ref2", type text}, {"Ref3", type text}})
      in
      #"Changed Type"

       

      Regards

       

       

       

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        RahulPBI ,

         

        Try this m code:

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDJCsMgEIbfJeccGpfYHs2maEuL0UMJeYOQW9+/IyodpDjgzPfPxmxbQy+i46xpm2h7mwGvQC9qcIPoyQg8cKQchnFMIhediOL5OY5Skf38rdOyLtN/wTmFBbBgrTUm0URm72atEdDq8Q4lloQyRkvTDDVlvO8raCGT3CuIN/pNUGpWymMwLd7jHbwxIRhTiI+9KUMnE9d4lQCLUI4H5IEvyM75+xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref1 = _t, Ref2 = _t, Ref3 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref1", type text}, {"Ref2", type text}, {"Ref3", type text}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Ref1", "Ref2", "Ref3"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","null",null,Replacer.ReplaceValue,{"Ref1", "Ref2", "Ref3"}),
        #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each let
        _1 = if [Ref1] <> null then [Ref1] else "",
        _2 = if [Ref2] <> null then [Ref2] else "",
        _3 = if [Ref3] <> null then [Ref3] else "" in
        _1 & " " & _2 & " " & _3),
        #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Trim, type text}})
        in
        #"Trimmed Text"