How we can refer previos row in power query formula

[Moncy]

Hi, how we can create custom colum with if condition formula referring previous or next rows.

for example (image pasted)

[Anonymous]

Hi @Moncy ,

I can give you an example to answer your question.
For example, I need to add a custom column column that will return 1 if the current row is larger than the next row, and 0 otherwise.

I'll copy the entire code from the Advanced Editor below (the main thing is to start with the #"IndexedSource" step, the rest of the steps are irrelevant)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7LDcAwCEN34dxD+KRNvArK/ms0ClD19mSeLdyJ0W9al1MDzwMKC3jQRp6sHOMDApZKeoJOqH0hJLq8TViw7JqCS8qJAbHfB+sF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Talk Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Talk Time", type duration}}),
    #"Inserted Total Seconds" = Table.AddColumn(#"Changed Type", "Total Seconds", each Duration.TotalSeconds([Talk Time]), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Total Seconds",{"Talk Time"}),
    #"IndexedSource" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"CustomColumn" = Table.AddColumn(IndexedSource, "Custom", each if [Index] = Table.RowCount(#"IndexedSource") - 1 then 
    0
    else 
        if #"IndexedSource"{[Index] + 1}[Total Seconds] < [Total Seconds] then 1 else 0)
in
    #"CustomColumn"

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Moncy]

thansk for your reply. I just managed with duplicating table creating Index1 & Index2 option

[lbendlin]

In Power Query you need to bring your own definition of what you mean by "previous" or "next".  The safest way is to add an index column (usually starting from 0).  Then you can reference the previous row by {[Index]-1} and the next row by {[Index]+1}.  You will also need to learn about try ... otherwise ...  because the first row has no previous row etc.

[Moncy]

thanks for your reply. worked out with Index option

[Vijay_A_Verma]

I have written a blog here which talks about ways to refer to previous row (same can be used for next row also with little tweak) - Power Query - Methods to Refer to Previous Row - Time Performance Comparison to determine the Best

[Moncy]

thansk for your reply, but its not working for my scenario

[zenisekd]

Hi, To create a custom column in Power BI with an IF condition that refers to previous or next rows, you can use the EARLIER() function to reference the previous row's value. If you want to reference the next row, you may consider creating a new table or utilizing a combination of functions like LEAD() or OFFSET().

E.G.: CustomColumn = IF( 'YourTableName'[Forecast Hours] > EARLIER('YourTableName'[Forecast Hours]), "Current row is greater than previous row", "Current row is not greater than previous row" )

Kudos and mark as solution appreciated 🙂