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)
