Parent Child Data Manipulation

Question

I have below dataThe requirement is to create new column(s) and insert the data from child rating as below, rating of child column should be inserted to the parent row&nbsp;&nbsp;&nbsp;

wanderingbi · Accepted Answer

From data modelling point of view I would not advise to create two rating columns.&nbsp;I would recommend to do it like this (use inner join to merge the table with itself):&nbsp;let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzckBUsZAHJBYlJpXohSrE61kBOSCZE2A2DkjMycFLGoMFTVEETVBmGKEaoop1ARUU8ygIqYoouY4TbEAcs2hTkSot4SKGqGIGhogjDFFNcYQ7CcDDIMMjWHiqM40xO0vQ1OElCGSVCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"RequestID -" = _t, RequestParentId = _t, Rating = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RequestID -", Int64.Type}, {"RequestParentId", Int64.Type}, {"Rating", Int64.Type}, {"Type", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"RequestID -"}, data, {"RequestParentId"}, "data", JoinKind.LeftOuter),
    #"Expanded data" = Table.ExpandTableColumn(#"Merged Queries", "data", {"RequestID -", "Rating"}, {"RequestID -.1", "Rating.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded data",{{"RequestID -.1", "RequestID_Child"}, {"Rating.1", "Rating_Child"}})
in
    #"Renamed Columns"&nbsp;&nbsp;&nbsp;Possible Matrix visualization:&nbsp;

Forum Discussion

Parent Child Data Manipulation

1 Reply

Related content

Parent/child hierarchy creation

Power Query - flatten Parent-child hierarchy

Parent pipeline invokes old version of child pipeline until parent is re-saved

Dax measure parent/child

Parent/Child Hierarchies visual with multiple parents