MCode: How do I add space if a column in my Table.AddColum if a column is blank?

[Anonymous]

Hi, I am new to Mcode. I have below formula and works well if all columns has value.

'=Table.AddColumn(#"Col3 type to Text", "Custom", each Text.Combine(List.Select({[Col1],[#"Col2"],[Col3],[#"Col4"],[#"Col5"]}, each _<> "" and _ <> null),""))

In some cases colum 3 is blank, how should I update my formula so that if column 3 is blank there should be a space in between Col2 and Col4 in my concatinatio? 

Should be 'Col1Col2 Col4Col5' rather that Col1Col2Col4Col5.

[lbendlin]

that is just some sample data based on your description.  Replace that part with your actual source.

[Anonymous]

@lbendlin  this worked like a charm 🙂 thanks!

I updated mine to below as formula goes into error if I added in#"Added Custom".

#"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine({[Col1],[Col2],if [Col3] = null or [Col3] = "" then " " else [Col3],[Col4],[Col5]}))

[Anonymous]

@lbendlin thanks for the response, should I also include below in my formula?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6VYHYQoQjAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t]),

[lbendlin · Accepted solution]

Something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6VYHYQoQjAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine({[Col1],[Col2],if [Col3] = null or [Col3] = "" then " " else [Col3],[Col4],[Col5]}))
in
    #"Added Custom"

You don't really need the List.Select for this.