If field is NULL then use other field

[Centaur]

Hello, 

I need to combine 2 columns based on if one of them is NULL. 

I will say it in English:

if [Name_FBL1N_Stampli] is Null then use [VendorList2.Name]

If [VendorList2.Name] is Null then use [Name_FBL1N_Stampli] 

Combine into 1 column.   

I have been fiddling around with it but its not giving me what I want.

thank you.

here is the advanced editor: 

let
Source = Table.FuzzyNestedJoin(VendorList_2, {"Name_FBL1N_Stampli"}, VendorList, {"COMPANY_NAME"}, "VendorList", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=.5]),
#"Reordered Columns" = Table.ReorderColumns(Source,{"Name_FBL1N_Stampli", "VendorList2.Vendor", "Vendor Code", "ConsolidateSTAMPLI.DrawNo", "ConsolidateSTAMPLI.Category", "VendorList"}),
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Custom", each if [VendorList2.Vendor] = "" then [Name_FBL1N_Stampli] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom"})
in
#"Removed Columns"

[Vijay_A_Verma]

Use this in a custom column

= if [Name_FBL1N_Stampli] is null then [VendorList2.Name] else [Name_FBL1N_Stampli]

[EaglesTony]

My solution was 2 custom columns, one checking for null and one checking for not null.

[watkinnc · Accepted solution]

How about a bit of Power Query technical talk that will help you figure it out? "" and null are not equal or interchangeable. I'm not sure if you have actual "" or nulls in your dataset.

Another very important thing to remember is that when combining text columns, any null value makes the whole combined text null. So Text.Combine("SomeText", null)//equals null

Yiu can replace the nulls first with some text value that you are sure will never be used to delimiter your text, like "ZZZZ".  Then combine your text, and then split off the "ZZZZ".

But I suspect that it's the tact combined with null that is the issue.

--Nate

[Centaur]

Hi, thank you.  I think the value is not NULL as you stated even though it says its NULL.  That formula provided by was the same formula I tried but it doesnt change the NULL value.  I have reverted to another strategy.   thank you.