Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I need to replace two null cells with the value in another cell in the same column, but I cannot figure out how to shift the index in the column or how to replace it without using a fixed number.
I have replaced the null with 'x', but it is the same even though there are data from different sources (with the same structure) that needs to have their content replaced with the cell 9 and 15 rows above each. Most important is the cells in the 22nd and 47nd (and recurring with an incrament of 25) so if those are the only ones that can be fixed I wouldn't mind, I'd just like to know and learn. I have tried to work with lists and measurements but haven't found a solution.
The pink is the value that needs to replace the red and the cyan is the value that needs to replace the blu/purple.
Does anyone have any experience in working like this or know of any way forward?
Solved! Go to Solution.
Hello - here is an example of how this can be solved. You can get the value of the nth row above, but this leaves the opportunity for retrieving the wrong value if there is an unexpected number of rows in the set above. This solution fills the null values in column 2 with the value of the first row above which has the same column 1 value.
SCRIPT
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2pEQAwDAPBXoQN8jhfLR7330YcA6GwBbqRGSoEEy6GFlqpHtopDZ3UCNWSfIkyGUw+xWbAvX4uFoMG9ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
RowIndex = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
RowIndex, "Custom", each
[Column2] ?? List.Last ( Table.SelectRows ( Table.FirstN ( RowIndex, [Index] - 1 ), (x)=> x[Column1]=[Column1] )[Column2] ), Int64.Type
)
in
#"Added Custom"
RESULT
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Thank you so much, it needed some tweaking but got me on the right track to reach my goal. (Only the first null in the each set of lists was replaced, solved it by removing the first one to focus on the second one.)
Thank you for your help!
You're welcome!
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Hello - here is an example of how this can be solved. You can get the value of the nth row above, but this leaves the opportunity for retrieving the wrong value if there is an unexpected number of rows in the set above. This solution fills the null values in column 2 with the value of the first row above which has the same column 1 value.
SCRIPT
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2pEQAwDAPBXoQN8jhfLR7330YcA6GwBbqRGSoEEy6GFlqpHtopDZ3UCNWSfIkyGUw+xWbAvX4uFoMG9ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
RowIndex = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
RowIndex, "Custom", each
[Column2] ?? List.Last ( Table.SelectRows ( Table.FirstN ( RowIndex, [Index] - 1 ), (x)=> x[Column1]=[Column1] )[Column2] ), Int64.Type
)
in
#"Added Custom"
RESULT
If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.
Proud to be a Microsoft Fabric Super User
Check out the July 2025 Power BI update to learn about new features.