cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
blokesh23
Helper I
Helper I

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

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_1-1643396146610.png

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1643399080388.png

 

Group:

AlexisOlson_1-1643399106026.png

 

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"

View solution in original post

4 REPLIES 4
blokesh23
Helper I
Helper I

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

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.

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

AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1643399080388.png

 

Group:

AlexisOlson_1-1643399106026.png

 

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"

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!