Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Centaur
Helper V
Helper V

If field is NULL then use other field

 

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.

 

Centaur_0-1649528026625.png

 

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"

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

Use this in a custom column

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

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

Anonymous
Not applicable

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

 

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. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors