DAX - getting rid of nulls when concatenating an address from 5

Resolved · Urgent Priority · Version 365

Thu 6 Apr 2023, 16:22 Delegate Sharlene said...

Sharlene has attended:
Power BI DAX course

DAX - getting rid of nulls when concatenating an address from 5

Hi there,

I have been trying to concatenate an address using 5 different columns. The code I am using does work but there are random commas hanging around were some of the rows have nulls in the cells please help I just cant figure out how to skip the null and continue on.

Here is an example of the code:
FullAdd =
Var Add1= 'table'[ADDRESS1]
Var Add2= 'table'[ADDRESS2]&","
Var Add3= " "&'table'[ADDRESS3] &", "
Var Cit= " "& 'table'[City]&","

Return

[ADDRESS1] & UNICHAR(10)
&if (NOT ISBLANK(Add2),Add2, Add3)&
if (NOT ISBLANK(Add3),Cit)&
if (ISBLANK(Cit),Cit) & table[County] & ", " & [zip]

an example of what I would get is something like
Shell House
, MidSommer, Sommerset, XX00 0YY

many thanks
Sharlene

Tue 11 Apr 2023, 17:08 Trainer Martin said...

RE: DAX - getting rid of nulls when concatenating an address fro

Hi Sharlene,

Thank you for your question and welcome to the forum.

Just so you know, the bank holiday has meant a delay in responding.

It would be really helpful if you could send a sample of your data (in Excel or Power BI desktop) to our email address (info@stl-training.co.uk) to work out how to solve the problem.

Looking forward to your response

Kind regards
Martin
(STL trainer)

Wed 26 Apr 2023, 10:16 Delegate Sharlene said...

RE: DAX - getting rid of nulls when concatenating an address fro

Hi Martin,

Thanks for your response! I sent over the data but forgot to update here. I look forward to hearing from you soon :)

Regards
Sharlene

Wed 26 Apr 2023, 15:47 Trainer Martin said...

RE: DAX - getting rid of nulls when concatenating an address fro

Hi Sharlene

I believe this is the answer to removing extra commas when concatenating using DAX:

1. Create a new column in the table in Power BI

2. Write the following (or copy and paste):

FullAdd1 =
Var Add1 = [ADDRESS1]
Var Add2 = [ADDRESS2]
Var Add3 = [Address3]
Var Cit = [City]
Var County = [County]

return

[address1]&", "&UNICHAR(10)
&if(isblank(Add2)&&isblank(Add3)&&ISBLANK(Cit),County&", "&[ZIP],
if(isblank(Add2)&&isblank(Add3),Cit&", "&[County]&", "&[ZIP],
IF(isblank(Add3)&&isblank(cit),add2&", "&[County]&", "&[ZIP],
IF(isblank(Add2),add3&", "&cit&", "&[County]&", "&[ZIP],
IF(isblank(Add3),add2&", "&cit&", "&[County]&", "&[ZIP],
Add2&", "&Add3&", "&Cit&", "&[County]&", "&[ZIP]
)))))

Note there are 5 IF statements as part of a nested IF. Each statement covers every combination of blank rows using && instead of AND (in Excel) for multiple AND conditions that exceed 2.

The formula is based on the test data you sent me where I have removed all leading spaces from the headings. It seemed not to work otherwise. I will send back the amended test data file shortly for you to connect to it again in Power BI and try out the above formula.

Hope it works your end. Please let me know if it doesn't or if it does work

Kind regards
Martin
(IT Trainer)

Sat 6 May 2023: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Master the Power BI Custom Visuals

Power BI offers a variety of custom visuals created by the community and Microsoft. Explore these visuals to enhance your reports and dashboards. Whether it's a custom map, a timeline, or a unique chart type, custom visuals can add significant value to your visualizations.

View all Power BI hints and tips