Hi Guys,
i have table like here:
ValueNumber
| ValA | 1 |
| ValA | 2 |
| ValA | 3 |
| ValB | 1 |
| ValB | 2 |
| ValC | 1 |
And now i want to add one more column and check if within specific value exists value "1".
If exists put 1 else put 0.
Expected result:
ValueNumberIf1Exists
| ValA | 1 | 1 |
| ValB | 1 | 1 |
| ValC | 1 | 1 |
| ValD | 3 | 0 |
Thank you for help,
Jacek
Hi Jacek,
Group the rows and aggregate the Number column by Minimum. This in itself will tell you if 1 exists, but if you want to create another column you can do so using an if statement
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}, {"Number", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Value"}, {{"Min", each List.Min([Number]), type nullable number}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Exists", each if [Min] = 1 then 1 else 0)
in
#"Added Custom"
Regards
Phil
Great,
thank you Philip!
Jacek
