Forum Discussion

gaikwadaa123's avatar
gaikwadaa123
Icon for Helper II rankHelper II
4 years ago

Difference of the values by rows

Hello,

Need urgent help. I have a data that looks like below: 

I want to calculate the diffrence between forecast values by rows. 0 hour will always be 0 and next hour will be the differnce between hour 0 and hour 1 for the forecast column and so on...

I would like to create the seperate column of the difference of the forecast values by hour. 

Thank you. 

Need Help

6 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    gaikwadaa123 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc8xCgQxDAPAv6ReRKLYsf2WsD9Yrrv/X0iK9VWGQQh5zlLLVT7f51knjAqLKPc1S0tedYD07XzdlQSF23vy5oT1k5fXVzXRRbdrcreAVNk+cp4VHmO75f420PtxTy4joKc+0vxmDsaZ0/7+DQF1/Xv/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hour = _t, Actual = _t, Forecast = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hour", Int64.Type}, {"Actual", type text}, {"Forecast", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Difference", each try [Forecast] -  #"Changed Type"[Forecast]{[Hour]-1} otherwise null)
in
    #"Added Custom"
    • gaikwadaa123's avatar
      gaikwadaa123
      Icon for Helper II rankHelper II
      4 years ago

      Thank you for your response. Do i have to do this step in blank query?

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        gaikwadaa123 

        Yes, Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes given and follow the steps.

        You can add the following as a new custom column to your table and change the "Changed Type"  to whatever previous step name you have in your query

         

        try [Forecast] -  #"Changed Type"[Forecast]{[Hour]-1} otherwise null

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi gaikwadaa123 ,

     

    Sorry for my late reply.

    According to my understanding, you want to calculate the difference between current hour and previous hour of each Measurement name , right?

     

    If so , I have built a simply data sample, please follow these steps:

    1.Duplicate the table--> Add a custom column: [Hour +1] --> Remove the original Hour column --> Rename the added column to Hour:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY6xDcAgDAR3cU2BDQZSJmtY7L9G3pH4NNadXjo5QqoUUce5ZZcQBV2TaqDRqQ1k/5qDVeqXyvVhygY1U21RM+WdmrSU6ucr6H4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Hour = _t, Value = _t, #"Measurement Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hour", Int64.Type}, {"Value", Int64.Type}, {"Measurement Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Hour]+1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Hour"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Hour"}})
in
    #"Renamed Columns"

    2. Merge these two tables:

    3. Sort row by Hour --> Add a custom column:

    =if [Hour] =0 then 0 else [Value]-[Value.1]

     

    Output:

     

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