cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LINKoscar
New Member

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

 

 

View solution in original post

3 REPLIES 3
LINKoscar
New Member

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!

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

 

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors