Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ritar_li
Frequent Visitor

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.

ritar_li_0-1732292047409.png

 

How can I implement this functionality?

Thank you in advance!

 

Hong

 

 

1 ACCEPTED SOLUTION
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1732502056418.png

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

vheqmsft_1-1732502101760.png

 

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

 

 

View solution in original post

7 REPLIES 7
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1732502056418.png

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

vheqmsft_1-1732502101760.png

 

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
Super User
Super User

Hi @ritar_li ,

This is my sample data:

Bibiano_Geraldo_0-1732309640129.png

 

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.

Bibiano_Geraldo_1-1732309929323.png

 

Now your table should look like this:

Bibiano_Geraldo_2-1732309983891.png

 

 

 

Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue

Thanks.

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

Hi,

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

 

Is this post help you? Please consider to:
Accept as Solution!
Give a Kudo
Follow me on Linkedin: Bibiano_Geraldo_Mangue

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

Power Query has "Fill Down"  actions for this.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.