Forum Discussion

ritar_li's avatar
ritar_li
Frequent Visitor
1 year ago
Solved

Fill out empty field with text strings in the previous row

Hello, 

I'm working on a project involving application reviews, where each applicant (identified by a record ID) may have multiple rows of data. Some rows are missing the applicant's name. When the name is missing, I would like to fill it in using the name from the previous row associated with the same record ID. See the attached screenshot.

 

How can I implement this functionality?

Thank you in advance!

 

Hong

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi ritar_li ,
    According to your description, you can first grouping operation and then fill down, the following is the sample data and code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBfC4IwFMW/ivgszG1E+WgGUVSIbyESQwUHpjKt6Nt3l382Uwkuu+fhnt89d2FoYtMyr/I5FLzhLAcFdWHxG9pelI/KcA2QxzIroDkII2ITacB2ryNL47hJcnOrSg78gragvEzwul2CwU4kgU4JQfrk6aud0wsTRLHykNn0ZybUUg+UzxoueMzUAeQr2/VkjBofMGGdeM26LN0H0Kl/Mb6NVspCZ9Pr5aD1zPg4oc/uac6GjDtQAS+HfZsFgB7xD4L0jOgD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person ID" = _t, Year = _t, Event = _t, Instance = _t, Name = _t, Departme = _t, Supervisor = _t, #"Completetion Date" = _t, #"Review Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person ID", Int64.Type}, {"Year", type text}, {"Event", type text}, {"Instance", Int64.Type}, {"Name", type text}, {"Departme", type text}, {"Supervisor", type text}, {"Completetion Date", type date}, {"Review Date", type date}}),
    ReplacedBlankWithNull = Table.ReplaceValue(#"Changed Type", "", null, Replacer.ReplaceValue, {"Name", "Departme", "Supervisor"}),
    #"Grouped Rows" = Table.Group(ReplacedBlankWithNull, {"Person ID"}, {{"All", each _, type table [Person ID=nullable number, Year=nullable text, Event=nullable text, Instance=nullable number, Name=nullable text, Departme=nullable text, Supervisor=nullable text, Completetion Date=nullable date, Review Date=nullable date]}}),
    FilledDown = Table.TransformColumns(#"Grouped Rows", {"All", each Table.FillDown(_, {"Name", "Departme", "Supervisor"})}),
    #"Expanded All" = Table.ExpandTableColumn(FilledDown, "All", {"Year", "Event", "Instance", "Name", "Departme", "Supervisor", "Completetion Date", "Review Date"}, {"Year", "Event", "Instance", "Name", "Departme", "Supervisor", "Completetion Date", "Review Date"})
in
    #"Expanded All"

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    Power Query has "Fill Down"  actions for this.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi ritar_li ,

    This is my sample data:

     

    To fill down values, open your table in Power Query, select the desired column, navigate to the Transform tab, click on the Fill dropdown, and choose Fill Down.

     

    Now your table should look like this:

     

     

     

    • ritar_li's avatar
      ritar_li
      Frequent Visitor
      1 year ago

      Thanks.

      Is it possible that I only fill down with 1 row?

      • ritar_li's avatar
        ritar_li
        Frequent Visitor
        1 year ago

        I meant to ask you how to fill down within ONE record ID instead of all empty cells?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ritar_li ,
    According to your description, you can first grouping operation and then fill down, the following is the sample data and code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBfC4IwFMW/ivgszG1E+WgGUVSIbyESQwUHpjKt6Nt3l382Uwkuu+fhnt89d2FoYtMyr/I5FLzhLAcFdWHxG9pelI/KcA2QxzIroDkII2ITacB2ryNL47hJcnOrSg78gragvEzwul2CwU4kgU4JQfrk6aud0wsTRLHykNn0ZybUUg+UzxoueMzUAeQr2/VkjBofMGGdeM26LN0H0Kl/Mb6NVspCZ9Pr5aD1zPg4oc/uac6GjDtQAS+HfZsFgB7xD4L0jOgD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person ID" = _t, Year = _t, Event = _t, Instance = _t, Name = _t, Departme = _t, Supervisor = _t, #"Completetion Date" = _t, #"Review Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person ID", Int64.Type}, {"Year", type text}, {"Event", type text}, {"Instance", Int64.Type}, {"Name", type text}, {"Departme", type text}, {"Supervisor", type text}, {"Completetion Date", type date}, {"Review Date", type date}}),
    ReplacedBlankWithNull = Table.ReplaceValue(#"Changed Type", "", null, Replacer.ReplaceValue, {"Name", "Departme", "Supervisor"}),
    #"Grouped Rows" = Table.Group(ReplacedBlankWithNull, {"Person ID"}, {{"All", each _, type table [Person ID=nullable number, Year=nullable text, Event=nullable text, Instance=nullable number, Name=nullable text, Departme=nullable text, Supervisor=nullable text, Completetion Date=nullable date, Review Date=nullable date]}}),
    FilledDown = Table.TransformColumns(#"Grouped Rows", {"All", each Table.FillDown(_, {"Name", "Departme", "Supervisor"})}),
    #"Expanded All" = Table.ExpandTableColumn(FilledDown, "All", {"Year", "Event", "Instance", "Name", "Departme", "Supervisor", "Completetion Date", "Review Date"}, {"Year", "Event", "Instance", "Name", "Departme", "Supervisor", "Completetion Date", "Review Date"})
in
    #"Expanded All"

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly