custom column to fetch values from multiple columns based on Id reference

[Anonymous]

Hi All,

I have a requirement to validate cell value (id), against multiple columns and return a calulated column saying the ID is part of which all applications. Something like below

Initial Data:

ID	Name	App1	App2	App3
1	Steve	1
2	paul		2
3	adam	3	3
4	smith	4	4	4

Post Applying Custom Column:

ID	Name	App1	App2	App3	App Details
1	Steve	1			App1
2	paul		2		App2
3	adam	3	3		App1,App2
4	smith	4	4	4	App1,App2,App3

Please help on how to build this requirement.

Regards,

Sivapratap.

[v-shex-msft · Accepted solution]

HI @Anonymous,

Maybe you can try to use following calculate column formula:

Detail=
CONCATENATEX (
    FILTER (
        {
            IF ( ISBLANK ( Table2[App1] ), "", "app1" ),
            IF ( ISBLANK ( Table2[App2] ), "", "app2" ),
            IF ( ISBLANK ( Table2[App3] ), "", "app3" )
        },
        [Value] <> BLANK ()
    ),
    [Value],
    ","
)

Regards,

Xiaoxin Sheng