Forum Discussion
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 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 |
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
4 Replies
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_kpiHi
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
Thanks for the help. It works for me.
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
