Forum Discussion
Conditionally Fill Down if the previous row value matches!
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??
| 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
Hi Anonymous ,
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 Custom1but 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 Custom1But this will only return correct result if the rows for each group already sit together without any gaps/breaks.
1 Reply
Hi Anonymous ,
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 Custom1but 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 Custom1But this will only return correct result if the rows for each group already sit together without any gaps/breaks.