Merging text columns fails if cell contains "null"?

[P_Crane31]

Hi again, 

I have merged a number of text type columns in Power Query to form a full mailing address in a single cell.

= Table.AddColumn(#"Sorted Rows", "mailing_address", each [address_line_1] &" "& [address_line_2] &" "& [city] &" "& [state_province] &" "& [us_zip])

In the data, the address_line_2 column is not always used. When it's not used for an entry, it seems to be randomly assigning "null" or simply a blank space.  

As you can see below, the when address_line_2 contains either data or blank the merge succeeds, but if the cell contains null, it fails. I have attempted replace values using an empty box or a single press of the space bar (no success).

However, if I simply remove the address_line_2 so that

 = Table.AddColumn(#"Sorted Rows", "mailing_address", each [address_line_1] &" "&  [city] &" "& [state_province] &" "& [us_zip])

the merge succeeds, so the "null" is killing me. 

How can I modify those nulls so the merges succeed?

Thank you all!!!

address_line_1	address_line_2	city	state	zip	MERGED address
123 any st	apt 555	baltimore	MD	12345	123 any st apt 555 baltimore md 12345
456 this st	null	pittspurgh	PA	54321	null
789 that st		atlanta	GA	98765	789 that st  atlanta GA 98765

[Poohkrd]

Hi! Try this one:

= Table.AddColumn(#"Sorted Rows", "mailing_address", let f = (t)=> if t="null" then "" else t in each f([address_line_1]) &" "& f([address_line_2]) &" "& f([city]) &" "& f([state_province]) &" "& f([us_zip]) )

[P_Crane31]

@Poohkrd SO, this one didn't work, but can you point me to some documentation to help me understand what it's supposed to do? I'm a mostly point-'n-click guy. I've just started learning "code". Specifically the 

let f = (t)=> if t="null" then "" else t in each

 I appreciate the assist!

[Poohkrd]

I thought you had "null" text values in the table. But I didn't guess, Imke guesses better. My code is the declaration of a function named "f", which checks each value for the equality of "null".

[ImkeF · Accepted solution]

Hi @P_Crane31 ,
if you use the function "Text.Combine" instead, null values will work just fine:
Text.Combine({[address_line_1], [address_line_2], [city], [state], [zip]}, " ")

[P_Crane31]

@ImkeF that worked like a charm! Thanks very much!!

[Anonymous]

Whaaaat?! @ImkeF I did not know that!!