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

[RahulPBI]

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

[camargos88]

Hi @RahulPBI ,

You can use GoogleDrive/OneDrive/Dropbox to upload it.

[RahulPBI]

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 · Accepted solution]

@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"

[RahulPBI]

@camargos88 

Perfect

Thank you, very much appreciated