Forum Discussion

NaudeJP's avatar
NaudeJP
New Member
6 years ago
Solved

Need Help On Getting Value From Previous Row

Hi Everyone,

 

I am new to PowerBI and please need help on a formula.

 

I am building a PowerBI fuel consumption model for a road freight company. I need to add a new column after the "Odometer" column on the query below. The value in the new column needs to be the previous row's value in the "Odometer" column- if the current value in the "Vehicle" column matches the value of the previous row's "Vehicle" column. I hope this makes sense!

 

 

This needs to be done in order for us to calculate the distance driven by a specific vehicle since the previous fuel fill-up.

  

Need Help
  • edhans's avatar
    edhans
    6 years ago

    Hi NaudeJP 

     

    Your original data shows you are in Power Query. This code sample will provide that data in Power Query before it loads to the data model.

    You can see it returns the previous odometer reading unless it is the first odometer reading for that vehicle, and then it returns null.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkMjYxOlWJ1oJSOYgLGJGVjAGMhJAmIjmIAJVIURUA9YwBSqwsTEFKgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnitID = _t, Vehicle = _t, Odometer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UnitID", Int64.Type}, {"Odometer", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"Vehicle"}, {{"All Rows", each _, type table [UnitID=number, Vehicle=text, Odometer=number]}}
            ),
    #"Added Index" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([All Rows], "Index", 0)),
    #"Added Prev Odometer" = 
        Table.AddColumn(
            #"Added Index", 
            "Added Prev Odometer", 
            each Table.AddColumn(
                [Added Index], 
                "Prev Odometer",
                let
                    varTable = [Added Index]
                in 
                each try varTable[Odometer]{[Index]-1} otherwise null
                )
            ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Prev Odometer",{"Added Prev Odometer"}),
    #"Expanded Added Prev Odometer" = Table.ExpandTableColumn(#"Removed Other Columns", "Added Prev Odometer", {"UnitID", "Vehicle", "Odometer", "Prev Odometer"}, {"UnitID", "Vehicle", "Odometer", "Prev Odometer"})
in
    #"Expanded Added Prev Odometer"

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

     

    A calculated column may work for your needs, but I generally try to avoid them if possible, but your question isn't a black and white issue as to where this goes. See which works best for you! In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
    Calculated Columns vs Measures in DAX
    Calculated Columns and Measures in DAX
    Storage differences between calculated columns and calculated tables

     

     

     

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    NaudeJP 

     

    I can't see the screenshot you provided clearly.

     

    Hope below example can help you.

     

    perviousvalue = MAXX(FILTER('Table','Table'[order]=earlier('Table'[order])-1),'Table'[test])

     

     

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Hi NaudeJP 

     

    Your original data shows you are in Power Query. This code sample will provide that data in Power Query before it loads to the data model.

    You can see it returns the previous odometer reading unless it is the first odometer reading for that vehicle, and then it returns null.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkMjYxOlWJ1oJSOYgLGJGVjAGMhJAmIjmIAJVIURUA9YwBSqwsTEFKgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnitID = _t, Vehicle = _t, Odometer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UnitID", Int64.Type}, {"Odometer", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"Vehicle"}, {{"All Rows", each _, type table [UnitID=number, Vehicle=text, Odometer=number]}}
            ),
    #"Added Index" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([All Rows], "Index", 0)),
    #"Added Prev Odometer" = 
        Table.AddColumn(
            #"Added Index", 
            "Added Prev Odometer", 
            each Table.AddColumn(
                [Added Index], 
                "Prev Odometer",
                let
                    varTable = [Added Index]
                in 
                each try varTable[Odometer]{[Index]-1} otherwise null
                )
            ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Prev Odometer",{"Added Prev Odometer"}),
    #"Expanded Added Prev Odometer" = Table.ExpandTableColumn(#"Removed Other Columns", "Added Prev Odometer", {"UnitID", "Vehicle", "Odometer", "Prev Odometer"}, {"UnitID", "Vehicle", "Odometer", "Prev Odometer"})
in
    #"Expanded Added Prev Odometer"

    1) In Power Query, select New Source, then Blank Query
    2) On the Home ribbon, select "Advanced Editor" button
    3) Remove everything you see, then paste the M code I've given you in that box.
    4) Press Done

     

    A calculated column may work for your needs, but I generally try to avoid them if possible, but your question isn't a black and white issue as to where this goes. See which works best for you! In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
    Calculated Columns vs Measures in DAX
    Calculated Columns and Measures in DAX
    Storage differences between calculated columns and calculated tables