Add new conditional column is not giving the results I want

[Anonymous]

Hi,

I have a dataset with 2 families and their respective members.

This includes a family column and a person column.

I want a third column to return TRUE if Simon is member of the family.

Right now the conditional column I added in query only return TRUE on the row where Simon is, but I want it to return TRUE on the rows which contain the family of Simon.

Take a look at representation of my problem on the picture below.

How can I acheive the column on the right (in power query)?

PS: The coloring are just to make the picture understandable, and are not meant to be part of the solution in query.

All responses and suggestions are appreciated and welcomed.

Thank you,

Frode 

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILCnKTM5WitWB8IMzc/Pz4DyPxLy8xAw41zexqBLO8cksTgRzjECcxNKi1Dw41yM1LwVuLEjAKz8DIeudmJtYBNFrDOQWpaYWlcN55alFcDbUMbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Family = _t, Person = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Family", Int64.Type}, {"Person", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Is Simon member", each [Person]="Simon"),
    List = List.Distinct(Table.SelectRows(#"Added Custom",each [Is Simon member]=true)[Family]),
    Custom1 = Table.ReplaceValue(#"Added Custom",each [Is Simon member],each List.Contains(List,[Family]),Replacer.ReplaceValue,{"Is Simon member"})
in
    Custom1

[Anonymous]

Hi, 

Thank you for your response.

As this is just a presentation of my problem, and I want to integrate the solution in a complex corporate dataset with other column names and values, I cant paste the code directly in.

But from what I understand, there are no simple fix to this problem? 

I cant solve this by using the add custom column or add conditional column functions? 

Thank you, 

Frode

[Vijay_A_Verma · Accepted solution]

The solution will display all your columns of previous step and will preserve all your previous steps. You just need to make use of these 2 steps after your previous step. You will need to replace #"Added Custom" with your previous step. This presumes that Is Simon Member column has already been added you. 

    List = List.Distinct(Table.SelectRows(#"Added Custom",each [Is Simon member]=true)[Family]),
    Custom1 = Table.ReplaceValue(#"Added Custom",each [Is Simon member],each List.Contains(List,[Family]),Replacer.ReplaceValue,{"Is Simon member"})

[Anonymous]

Thanks, okay, I will try this next work day on monday, and come back here for further comments or solution acceptance 🙂