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 

slorin's avatar
slorin
Super User
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 

prashantg364's avatar
prashantg364
Helper II
3 years ago

Thanks for the help. It works for me.