Conditional Column

[dw700d]

I have two columns, A parent column which gives the name of each parent and a child column that represents the type of child each parent has. I would like to create a new column within the query editor that tells me if a parent has a toddler, an infant or both. For example the new column would  show that John has “both”, Tom has “Toddler”, Mike has “infant” and Bob has “Both”

Parent	Child
John	Toddler
John	infant
John	infant
Tom	Toddler
Tom	Toddler
Tom	Toddler
Mike	infant
Mike	infant
Mike	infant
Bob	Toddler
Bob	infant
Bob	infant

[jeffshieldsdev · Accepted solution]

This should work:

Note: your third "Tom" has a trailing space. Also, you may be aware Power Query string comparisons are case sensitive by default.

let
    // Your table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQrJT0nJSS1SitWBC2XmpSXmleAQCcnPRdNFjIhvZnYqqjnEiDjlJ6GZAxHBUAITiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", type text}, {"Child", type text}}),

    // Group rows by "Parent" into Data column
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent"}, {{"Data", each _, type table [Parent=nullable text, Child=nullable text]}}),

    // Determine Child Type by rows in Data column
    #"Added Child Type" = Table.AddColumn(#"Grouped Rows", "Child Type", each 
        if Table.ContainsAll([Data], {[Child = "Toddler"], [Child = "infant"]}) 
            then "both" 
            else if Table.Contains([Data], [Child = "Toddler"]) 
                then "Toddler" 
                else if Table.Contains([Data], [Child = "infant"]) 
                    then "infant"
                    else null,
    type text),

    // Expanded back Data to Child
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Child Type", "Data", {"Child"}, {"Child"})
in
    #"Expanded Data"

[jianlong]

1, duplicate the table under edit query
2, find one table to groupby "Parent",

3, Merge the two tables together

4, if or switch true create a new column..