Concatenate distinct non blank values in Power BI

userazRl1 0
Viewed 32

I would like to concatenate the data from source column when Id is the same, while excluding blanks. (Expected result shown below). I want to create a measure and I've used the following measure, which is giving me additional delimiters where there are nulls (For eg: a,,b or ,a,b). Could you let me know the best way to do this?

Concat =
CONCATENATEX (
    VALUES ( 'Table'[Source] ),
    'Table'[Source],
    ", "
)

Input

Id Source
1 Excel
1 SAP
1 Axalant
2 SAP
2
2 SAP

Expected Result

Id Source
1 Excel, SAP, Axalant
2 SAP
concatenation powerbi
1 Answers

The solution is to first filter out blanks from the table. Since VALUES() only accepts columns as input, you also have to switch to DISTINCT(), which accepts table inputs too. (The result of FILTER() is a table.)

ConcatNoBlanks = 
CONCATENATEX(
    DISTINCT(
        FILTER(
            'Table',
            'Table'[Source] <> BLANK()
        )
    ),
    'Table'[Source],
    ", "
)

enter image description here

Note that the extra "," in the 2nd row is gone now.

userhiPt0
Related