Custom Column based on Total number of values exists for an ID in multiple Row/Columns

[blokesh23]

In PowerQuery I want to add a custom column that provides total number of values exists for a correpsonding ID and create a custom column based on the value

For example,  new custom column(Total Child) should be created based on total number of value exists in (Child1 + Child2 + Child3 ) for a corresponding ID(Parent) and if it is greater than 1 it should display as Yes or No in the column Child More than 1 .
Below screen shot is what I'm trying to create 

[blokesh23]

Thanks, this worked as expected . Just want to check is there any approach apart from group/summarize and then merging back into the original table ., like can it be done directly using original table

[AlexisOlson]

There are certainly other possible methods. You could add a custom column like this instead:

(row) =>
    List.NonNullCount(
        List.Combine(
            Table.ToColumns(
                Table.RemoveColumns(
                    Table.SelectRows(
                        #"Changed Type",
                        each [Parent] = row[Parent]
                    ),
                    "Parent"
                )
            )
        )
    )

However, this will be much less efficient for larger sets of data since it has to filter the entire table for each row. The method I suggested earlier should scale better.

[blokesh23]

Thank you , then will prefer Approach1 which was suggested earlier as we are dealing with larger data sets.

[AlexisOlson · Accepted solution]

You can unpivot the child columns and then do a group by on the parent column with a count aggregation on the unpivoted child column and then merge that result back with the original table.

Unpivot:

Group:

Full sample query you can paste into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTI0AhEmQEIpVgchagwSANKmMFFDEM8MRJgjqQVrtoCohYnBtFpiigGRkQFMzAQqYIikzhShzkgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Child1 = _t, Child2 = _t, Child3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", Int64.Type}, {"Child1", Int64.Type}, {"Child2", Int64.Type}, {"Child3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Parent"}, "Child", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Parent"}, {{"Total Child", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Parent"}, #"Grouped Rows", {"Parent"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Total Child"}, {"Total Child"})
in
    #"Expanded Grouped Rows"