Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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
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
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:
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
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
Power Query has "Fill Down" actions for this.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
124 | |
77 | |
74 | |
58 | |
49 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |