98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
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
Resolved · Urgent Priority · Version 365
Thu 6 Apr 2023, 16:22Delegate 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:08Trainer 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:16Delegate 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:47Trainer 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.
|
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Master the Power BI Custom VisualsPower 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. |
