Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

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

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

4 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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's avatar
      AlienSx
      Super User
      2 years ago

      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's avatar
    AlienSx
    Super User
    2 years ago

    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.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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