Forum Discussion
MCode: How do I add space if a column in my Table.AddColum if a column is blank?
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.
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.
4 Replies
- lbendlin
Super User
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.
- lbendlin
that is just some sample data based on your description. Replace that part with your actual source.
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]}))
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]),