Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries