Fill out empty field with text strings in the previous row

[ritar_li]

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

[Anonymous · Accepted solution]

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

[Bibiano_Geraldo]

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]

Thanks.

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

[Bibiano_Geraldo]

Hi,

Please give me more details to better assist you, if you have examples of output you can share here

[ritar_li]

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

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file

[lbendlin]

Power Query has "Fill Down"  actions for this.