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 |
Solved! Go to 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]}, " ")
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
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".
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
Whaaaat?! @ImkeF I did not know that!!