dax getting rid

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » DAX - getting rid of nulls when concatenating an address from 5

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

resolvedResolved · Urgent Priority · Version 365

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

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)

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

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

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

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


Server loaded in 0.07 secs.