How to fill in blanks in one column with the previous raw value from another column

[SNB]

Hello all, I need help, pleaseee...
Is there a way in Power Query to fill in blanks in one column with the previous raw value from another column? Please see example below. Thank you all!

[wdx223_Daniel · Accepted solution]

=Table.Combine(Table.Group(YourTable,"Rank",{"n",each Table.ReplaceValue(_,null,(x)=>[Institution]{0},Replacer.ReplaceValue,{"Rank"})},0,(x,y)=>Byte.From(y<>null))[n])

[v-cgao-msft]

Hi @SNB ,

Please try this:

let
    Source = Table.FromRecords({
        [Rank = 1, Institution = "AA"],
        [Rank = null, Institution = "BB"],
        [Rank = null, Institution = "CC"],
        [Rank = null, Institution = "DD"],
        [Rank = 2, Institution = "EE"],
        [Rank = null, Institution = "FF"],
        [Rank = null, Institution = "GG"]
    }),
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Custom1 = Table.FromRecords(Table.TransformRows(AddIndex, (x)=> if x[Rank] = null then [Rank=AddIndex[Institution]{x[Index]-1},Institution=x[Institution],Index=x[Index]]  else x))
in
    Custom1

Please consider lbendlin's suggestion to try to add Index columns in the data source

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum‘

[SNB]

Unfortunately, my list of institutions is too long, I will not be able to manually rank each. And I need for all the blanks to be equal to the same value. In your example, for Rank 1, it is AA, for Rank 2 it is EE, etc. Thank you for looking into that though!

[Omid_Motamedise]

Add a custome column based on the next formula.

if [Rank]=null then null else [Institution]

then use fill down command (from transfrom tab) for this new column.


If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

[SNB]

Thank you for advice, but this will not do what I need 😞

[Omid_Motamedise]

Which part of the result is different?

It can do what you explained on the question by adding a new column, then you can remove the last column in more efficient way. using index collumn and refering to the previous step is really time consuming.

[lbendlin]

Power Query has no idea what you mean by "Previous".  Bring your own index column (starting from 0) and then use that to address the prior row's content.

Ideally that index column should come from the source data as Power Query gives no sorting guarantee.