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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
P_Crane31
Frequent Visitor

Merging text columns fails if cell contains "null"?

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_1address_line_2citystatezipMERGED address
123 any stapt 555baltimoreMD12345123 any st apt 555 baltimore md 12345 
456 this stnullpittspurghPA54321null
789 that st atlantaGA98765789 that st  atlanta GA 98765
1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion

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]}, " ")

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Poohkrd
Advocate I
Advocate I

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]) )

 

@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!

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
Community Champion
Community Champion

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]}, " ")

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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