Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Replacing cells with content from cells within the same column

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?

 

LINKoscar_0-1679068038098.png

1 ACCEPTED SOLUTION
jennratten
Super User
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

jennratten_0-1679167008954.png

 

 

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

jennratten
Super User
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

jennratten_0-1679167008954.png

 

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors