Using ConcatenateX with other function to ignore blank values

[naveeddil]

Dear Experts,

To get unique and summarize result of a value in pivot tables i am using below command in to get distinct values from the data.

=CONCATENATEX(VALUES(Table1[ModalityCode]), Table1[ModalityCode] ,", ")

and i get the output as APPLES, ORANGES

As the data is very lengthy and there are suppose to be blanks values in the [ModalityCode] and it wasn't there in some months, so when there are blank values in start or end of the data so then i get the data as

, APPLES, BANANA
, APPLES
ORANGES, APPLES,
BANANA,
APPLES,


I want to customize the above CONCATENATEX and to eliminate the initial or ending delimeter if there are blank values.

 

[naveeddil]

It doesnt make sense but let me mention that i used below

=IF(ISBLANK(Table1[ModalityCode]),"",CONCATENATEX(VALUES(Table1[ModalityCode]), Table1[ModalityCode] ,", "))

and got the error that "This formula is invalid or incomplete: 'Calculation error in measure 'Table1'[ModalityJoINT]: A single value for column 'ModalityCode' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'."

 

[RoryA]

Actually it does make sense since you passed an entire table column as the first argument of an IF function, and you can't do that. Try filtering your table:

=CONCATENATEX(VALUES(FILTER(Table1[ModalityCode],NOT(ISBLANK(Table1[ModalityCode])))), Table1[ModalityCode] ,", ")

 

[naveeddil]

Thanks for the response.

Its having almost same error.

This formula is invalid or incomplete: 'Calculation error in measure 'Table1'[ModalityJoINT]: A single value for column 'ModalityCode' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'.

 

[RoryA]

Apologies, I put the FILTER in the wrong place:

=CONCATENATEX(FILTER(VALUES(Table1[ModalityCode]),NOT(ISBLANK(Table1[ModalityCode]))), [ModalityCode] ,", ")

 

[naveeddil]

Apologies, I put the FILTER in the wrong place:

=CONCATENATEX(FILTER(VALUES(Table1[ModalityCode]),NOT(ISBLANK(Table1[ModalityCode]))), [ModalityCode] ,", ")

Sorry i got delayed as i had issues installing in XL2BB to attach the sheet
As evident in below tables, its still showing trailing commas

Sample ConcatenateX.xlsx
	A	B	C	D	E
3	Region	PROVINCE	Year	Month	ModalityJOINT
4	Region X	Province 2	2021	Jan	, Inaccessbile
5	Region X	Province 2	2021	Feb
6	Region X	Province 2	2021	Mar	, Inaccessbile
7	Region X	Province 2	2021	Jun	, Inaccessbile
8	Region X	Province 2	2021	Nov	M2M
9	Region X	Province 2	2021	Dec	Inaccessbile
10	Region X	Province 2	2022	Jan	, M2M, Mix
11	Region X	Province 2	2022	Feb	Inaccessbile
12	Region X	Province 2	2022	Mar	, Others, H2H
13	Region X	Province 2	2022	May	, Others, Inaccessbile, H2H
14	Region X	Province 2	2022	Jun	, Others, H2H
15	Region X	Province 2	2022	Jul	, Others, H2H
16	Region X	Province 2	2022	Sep	, Others, H2H
17	Region X	Province 2	2022	Oct	, Others, H2H
18	Region X	Province 2	2022	Dec	, Others, H2H
19	Region X	Province 2	2023	Jan	H2H, Others,
20	Region X	Province 2	2023	Mar	Others,
21	Region X	Province 2	2023	May	Others,
22	Region X	Province 2	2023	Jul	Others,
23	Region X	Province 2	2023	Aug	Others,
24	Region X	Province 2	2023	Sep	Others,
25	Region X	Province 2	2023	Oct	Others,
26	Region X	Province 2	2023	Nov	Others,
27	Region Y	Province 1	2021	Jan	, Inaccessbile, H2H
28	Region Y	Province 1	2021	Mar	, Inaccessbile, H2H
29	Region Y	Province 1	2021	Jun	, Inaccessbile, H2H
30	Region Y	Province 1	2021	Nov	, H2H
31	Region Y	Province 1	2021	Dec	, H2H
32	Region Y	Province 1	2022	Jan	, H2H, Others
33	Region Y	Province 1	2022	Feb	, H2H, Others
34	Region Y	Province 1	2022	Mar	, H2H, Others
35	Region Y	Province 1	2022	May	, H2H
36	Region Y	Province 1	2022	Jun	, H2H
37	Region Y	Province 1	2022	Jul	, H2H
38	Region Y	Province 1	2022	Sep	, H2H
39	Region Y	Province 1	2023	Mar	H2H, Others,
40	Region Y	Province 1	2023	May	H2H,
41	Region Y	Province 1	2023	Aug	H2H,
42	Region Y	Province 1	2023	Sep	H2H,
Province

 

[RoryA]

It worked for me so I'd have to assume your codes are not actually blank.

 

[naveeddil]

There are blank in some months as We have not activities during some months thats why when it concatenate so there are blanks