Fill empty cell with value contained within the same column but with a condition within another colum

[Syndicate_Admin]

Hello everybody,

I'm starting my journey with PowerQuery and I'm currently facing an issue that could be solved with your suuport.

I have a table containing various requests (column id). Some of these requests are linked to a parent request (column parent_id). See below:

id - parent_id - DATA

1          1              A

2          1             

3          5             

4          1             

5          5             B

My goal is to fill the empty cells of the DATA column with the values of the cells which are not empty and in relationship with the parent request. 

Please find below the final table I'm looking for:

id - parent_id - DATA

1          1              A

2          1              A

3          5             B

4          1             A

5          5             B

Thanks in advance for your help.

Rémi

[Syndicate_Admin]

Hello,

It also works perfectly, thanks again 🙂 !

Yesterday I tried your first solution with a bigger db and I succeed so it's a very good news.

Rémi

[Syndicate_Admin]

Hello,

Thanks for your quick answer.

After some adjustments to fit with my source file, it works perfectly :).

Now I have to adapt it to a bigger db !

Thanks a lot for your support :)!

Rémi

[AlienSx]

Great! By I should have started with something simple. Like Table.Group and Table.FillUp (or FillDown). Try this:

let
    Source = your_table,
    #"Grouped Rows" = 
        Table.Group(
            Source, "parent_id", 
            {{"rows", each Table.FillUp(Table.Sort(_, "DATA"), {"DATA"})}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"id", "DATA"})
in
    #"Expanded rows"

[AlienSx]

Hello, RVIG

one of several options to solve this:

let
    Source = your_table,
    values = Table.SelectRows(Table.Distinct(Source[[parent_id], [DATA]]), each [DATA] <> null),
    dict = Record.FromList(values[DATA], List.Transform(values[parent_id], Text.From)),
    helper_column = 
        Table.AddColumn(
            Source, "helper",
            (x) => 
                if x[DATA] = null 
                then Record.FieldOrDefault(dict, Text.From(x[parent_id]), null)
                else x[DATA]
        )
in
    helper_column

then remove "DATA" column and rename "helper" as you want. Instead of exta (temporal) column one may choose to use Table.ReplaceValue (proved to be slower than other options) or a combination of Record.TransformFields and Table.TransformRows functions.