Difference of the values by rows

[gaikwadaa123]

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. 

[v-eqin-msft]

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.

[Fowmy]

@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]

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

[Fowmy]

@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

[gaikwadaa123]

Thank you for helping with this, but the issue is hours are not matching correctly. 

I need to consider the additional column which is below as 'Measurement Name'

Each measuremnt name has 23 hours. I want the diffrence of the rows by hour for each measurement nams. 

Can you send a code with this please?

I really appriciate 

[mahoneypat]

Please see this video for one way to do this.  You can use your Hour column in place of the Index column mentioned in video.

(9) Reverse cummulative values in Power Query - YouTube

Pat