Conditionally Fill Down if the previous row value matches!

[Jared__]

Hello everyone, 

I'm working with some project data and i have the task of consolidating the data for previous month. However, the dataset look like the below due to the merged cells in excel . The issue is that when filling down "Project 1" Values continue to fill down on the null values for Project 2 . Is there any way to write a conditional statement to solve this??

Current State

Project Name	Deliverables	Value
Project 1	Task 1	75
Project 1	Task 1	null
Project 2	Task 2	null
Project 3	Task 3	20

Future State:

Project Name	Deliverables	Value
Project 1	Task 1	75
Project 1	Task 1	75
Project 2	Task 2	null
Project 3	Task 3	20

I would really appreciate the help 

[ImkeF · Accepted solution]

Hi @Jared__ ,
if you use the "All" aggregation type in a group-operation, you will get all the rows that belong to your group columns. This allows you to limit the scope of the fill down operation to your desired values.
the save way to go would be this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRCkkszgYzzE2VYnVwyOWV5uSgyBrBZI2wyRrDZEEMIwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" Project Name" = _t, Deliverables = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" Project Name", type text}, {"Deliverables", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {" Project Name"}, {{"partition", each Table.FillDown(_,{"Value"})}}),
    Custom1 = Table.Combine(#"Grouped Rows"[partition])
in
    Custom1

but if your data is sorted by Project name already in the source and performance is an issue, then you can speed things up using the GroupKind.Local option like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRCkkszgYzzE2VYnVwyOWV5uSgyBrBZI2wyRrDZEEMIwOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" Project Name" = _t, Deliverables = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" Project Name", type text}, {"Deliverables", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {" Project Name"}, {{"partition", each Table.FillDown(_,{"Value"})}}, GroupKind.Local),
    Custom1 = Table.Combine(#"Grouped Rows"[partition])
in
    Custom1

But this will only return correct result if the rows for each group already sit together without any gaps/breaks.