Power query or DAX

[prashantg364]

Hi all,

I have a data set as shown in the input table,

Against each product name & product band, I have 2 KPI categories, 1. 3 KPI and 2. 5 KPI. In columns 3 & 4 I have total pass KPIs in each category. In the following few columns I have the details for failed KPIs in terms of 1 & 0. 1 means KPI is pass & 0 means KPI is fail.

Now I want to summarize my table where I can have only one row for each Product name & Product band combination, followed by pass KPI count in the next 2 columns for 2 different categories, and in the next 2 columns I want the failed KPI names separated by commas, as shown in output table.

Pls help, how I can do this either through power Query editor or by using some DAX.

Input table

Product name	Product band	KPI Category - 3 KPI(KPI1,KPI2,KPI3)	KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)	KPI1	KPI2	KPI3	KPI4	KPI5
Car	7	3	5	1	1	1	1	1
Car	3	1	2	1	0	0	0	1
Bike	7	2	2	1	1	0	0	0
Bike	3	3	3	1	1	1	0	0
Cycle	7	3	4	1	1	1	1	0
Cycle	3	0	0	0	0	0	0	0

Output table

Product name	Product band	Total pass in 3 KPI	Total pass in 5 KPI	Failed KPI -KPI Category - 3 KPI(KPI1,KPI2,KPI3)	Failed KPI - KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)
Car	7	3	5
Car	3	1	2	KPI2,KPI3	KPI2, KPI3, KPI4
Bike	7	2	2	KPI3	KPI3, KPI4, KPI5
Bike	3	3	3		KPI4, KPI5
Cycle	7	3	4		KPI5
Cycle	3	0	0	KPI1,KPI2,KPI3	KPI1,KPI2,KPI3,KPI4,KPI5

[slorin · Accepted solution]

Hi

Add new columns

=Text.Combine(List.Transform({{[KPI1],"KPI1"},{[KPI2],"KPI2"},{[KPI3],"KPI3"}}, each if _{0}=0 then _{1} else null), ",")

and

Text.Combine(List.Transform({{[KPI1],"KPI1"},{[KPI2],"KPI2"},{[KPI3],"KPI3"},{[KPI4],"KPI4"},{[KPI5],"KPI5"}}, each if _{0}=0 then _{1} else null), ",") 

Stéphane 

[prashantg364]

Thanks for the help. It works for me.

[Punithurs]

Hi prashantg364,
If you want to achieve this with the simple calculated column using dax. 

Step 1: 
Concatenate the columns.

Fail 3 = CONCATENATE(Sheet1[KPI1],CONCATENATE(Sheet1[KPI2],Sheet1[KPI3]))
And,

Text 3 = IF(Sheet1[Fail 3] = "111","",

IF(Sheet1[Fail 3] = "110","KPI3",

IF(Sheet1[Fail 3] = "100","KPI2,KPI3",

IF(Sheet1[Fail 3] = "001","KPI1,KPI2",

IF(Sheet1[Fail 3] = "010","KPI1,KPI3",

IF(Sheet1[Fail 3] = "011","KPI1",

"KPI1,KPI2,KPI3"))))))

We can use the same technique for other column as well 

[AlienSx]

Hello, @prashantg364 

let
    Source = your_table,
    lst03 = {"KPI1", "KPI2", "KPI3"},
    lst05 = {"KPI1", "KPI2", "KPI3", "KPI4", "KPI5"},
    f = (r as record, lst as list) =>
        List.Accumulate(
            lst,
            {},
            (s, c) => s & (if Record.Field(r, c) = 0 then {c} else {})
        ),
    comb = Table.CombineColumnsToRecord(Source, "KPI", lst05),
    txf = Table.TransformColumns(comb, {"KPI", (x) => f(x, lst05)}),
    failed3 = 
        Table.AddColumn(
            txf, "Failed KPI -KPI Category - 3 KPI(KPI1,KPI2,KPI3)", 
            (x) => Text.Combine(List.Select(x[KPI], (w) => List.Contains(lst03, w)), ", ")
        ),
    failed5 = Table.AddColumn(failed3, "Failed KPI - KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)", each Text.Combine([KPI], ", ")),
    remove_kpi = Table.RemoveColumns(failed5,{"KPI"})
in
    remove_kpi