Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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?

 

Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    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

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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!

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    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