Forum Discussion

prashantg364's avatar
prashantg364
Helper II
3 years ago
Solved

Power query or DAX

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 c...
Need Help
  • slorin's avatar
    slorin
    3 years ago

    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 

AlienSx's avatar
AlienSx
Super User
3 years ago

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