Forum Discussion
Combine Multiple Columns Only If No Value is Null
Super UserAnonymous you need to check the null value before combining
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]}, ":")
✨ Follow us on LinkedIn and to our YouTube channel
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 ❤ 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!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
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.
I.e.
This is for a PO combine, I also have a GR Combine where the part number would be needed later.
If I put your solution in for the PO, would it make the PN column null in the table altogether?
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)