How to populate parent values in the child key

[Kish1999]

Hello All,

I have requirement where i have 2 levels of hierarchy as shown below:

Key	Parent	Container
1	1	A
2	1
3	1
4	1
5	5	B
6	5
7	5
8	5

The requirement is to get the value of container field populated in all the child keys. This will help me roll up the total hours in the hour table which stores hours for each key, but i have show the hours for each container. Can you please help me achieve this. I tried doing some merges and getting the value but was not able to get this.

Regards

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWNHpVidaCUjKE8BzDNG4Zmg8EyBLBB2AvPMoDyInDkKzwLBiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Parent = _t, Container = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Parent", Int64.Type}, {"Container", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Container],(k)=> if k[Container]>" " then k[Container] else Table.SelectRows(#"Changed Type",each [Key]=k[Parent]){0}[Container],Replacer.ReplaceValue,{"Container"})
in
    #"Replaced Value"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".