Combine Multiple Columns Only If No Value is Null

Question

Hello All,&nbsp;&nbsp;I am attempting to combine multiple columns (simple), but need the output to be null if any of the three combined columns has a null value.&nbsp;&nbsp;Example:Current OutputPO No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PO Line No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; PO Part No&nbsp; &nbsp; &nbsp; &nbsp; | PONO_POLINE_POPRTNO (Output Column)PO3-00012-2021&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| PO3-00012-2021:1:123456null&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 123456&nbsp;Current Script:#"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "PONO_POLINE_POPRTNO", each Text.Combine({[POPR_PO_NO], Text.From([POPR_LINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),&nbsp;&nbsp;Desired Output:PO No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PO Line No&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; PO Part No&nbsp; &nbsp; &nbsp; &nbsp; | PONO_POLINE_POPRTNO (Output Column)PO3-00012-2021&nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| PO3-00012-2021:1:123456null&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp;123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| null&nbsp;&nbsp;&nbsp;Any/All assistance welcomed and much appreciated.&nbsp;&nbsp;Thank you in advance.&nbsp;

parry2k · Answer

Anonymous&nbsp;you need to check the null value before combining
&nbsp;
if [POPR_PO_NO] = null or [POPR_LINE_NO] = null or [MRDTL_REQ_PART_NO]} = null then null else

Text.Combine({[POPR_PO_NO], Text.From([POPR_LINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":")
&nbsp;
&nbsp;

✨ Follow us on LinkedIn and&nbsp;&nbsp;to our YouTube channel 
&nbsp;
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤&nbsp;Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation! 
&nbsp;
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

stevehailey · Answer

Hello,&nbsp;This should do the trick:&nbsp;&nbsp;= Table.AddColumn(#"Inserted Merged Column1", "PONO_POLINE_POPRTNO", each if ([POPR_PO_NO] is null or [POPR_LINE_NO] is null or [MRDTL_REQ_PART_NO] is null) then null else Text.Combine({[POPR_PO_NO], Text.From([POPR_LINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),&nbsp;&nbsp;

anonymous · Answer

parry2k&nbsp;&nbsp;Thank you as this is an excellent solution as I provided the question. What I left out, is that I need those values in other iterations of a combine.&nbsp;&nbsp;I.e.&nbsp;This is for a PO combine, I also have a GR Combine where the part number would be needed later.&nbsp;&nbsp;If I put your solution in for the PO, would it make the PN column null in the table altogether?&nbsp;

anonymous · Answer

if [POPR_PO_NO] = null or [POPR_LINE_NO] = null or [MRDTL_REQ_PART_NO] = null then null else
Text.Combine({[POPR_PO_NO], Text.From([POPR_LINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded MR_MRDTL_MATRL_REQ_PRT_DTL", "MREQ_MREQLINE", each Text.Combine({[MRDTL_MREQ_NO], Text.From([MRDTL_LINE_NO], "en-US")}, ":"), type text),
#"Inserted Merged Column1" = Table.AddColumn(#"Inserted Merged Column", "PRNO_PRLINE_PRPRTNO", each Text.Combine({[PRMRH_PR_DOC_NO], Text.From([PRMRH_PR_DOC_LINENO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),
#"Inserted Merged Column2" = Table.AddColumn(#"Inserted Merged Column1", "PONO_POLINE_POPRTNO", each Text.Combine({[POPR_PO_NO], Text.From([POPR_LINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),
#"Inserted Merged Column3" = Table.AddColumn(#"Inserted Merged Column2", "GRNO_GRLINE_GRPRTNO", each Text.Combine({[GIPD_RCPT_NO], Text.From([GIPD_RCPT_LINENO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),
#"Inserted Merged Column4" = Table.AddColumn(#"Inserted Merged Column3", "STNO_STLINE_STPRTNO", each Text.Combine({[INTERDT_STKTRANSFER_NO], Text.From([INTERDT_LINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),
#"Inserted Merged Column5" = Table.AddColumn(#"Inserted Merged Column4", "SINO_SILINE_SIPRTNO", each Text.Combine({[ISUID_TRANSACTION_NO], Text.From([ISUID_PRT_SYSLINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),
#"Inserted Merged Column6" = Table.AddColumn(#"Inserted Merged Column5", "SRNO_SRLINE_SRPRTNO", each Text.Combine({[STRECDT_RECEIPT_NO], Text.From([STRECDT_PRT_SYSLINE_NO], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text),
#"Inserted Merged Column7" = Table.AddColumn(#"Inserted Merged Column6", "ISNO_ISLINE_ISPRTNO", each Text.Combine({[ISUID_TRANSACTION_NO.1], Text.From([ISUID_PRT_SYSLINE_NO.1], "en-US"), [MRDTL_REQ_PART_NO]}, ":"), type text)

Forum Discussion

Combine Multiple Columns Only If No Value is Null

4 Replies

✨ Follow us on LinkedIn and to our YouTube channel

Related content

Combine Column

Combining Column Data

Combine columns into 1

Tables combine

Combining Multiple Column Into 1 Column