Forum

Checking if after g...
 
Notifications
Clear all

Checking if after grouping table there is specific value

3 Posts
2 Users
0 Reactions
159 Views
(@jaryszek)
Posts: 177
Reputable Member
Topic starter
 

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

 
Posted : 01/10/2021 6:08 am
Philip Treacy
(@philipt)
Posts: 1629
Member Admin
 

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

 
Posted : 01/10/2021 9:33 pm
(@jaryszek)
Posts: 177
Reputable Member
Topic starter
 

Great,

thank you Philip!

 

Jacek

 
Posted : 02/10/2021 1:58 am
Share: