Find difference of last two not null values

[prateekraina]

Hi Guys,

I need to find out difference of last two not null values in M Query.
Below table will explain what i require:

				What i require
X	Y	Z	Index	Xval
null	null	null	null	null
null	null	null	null	null
1	2	5	1	null or 0
null	null	null	null	null
null	null	null	null	null
3	1	1	2	2
2	6	8	3	-1
null	null	null	null
4	4	4	4	2

i have tried below M Query but i am not getting desired result. Kindly help.

= Table.AddColumn(#"Changed Type1", "xval", each if [Index] > 0 then [x] - #"Changed Type1"{[Index] - 1}[x] else 0)

@ImkeF Hope you can solve this as well 🙂

Prateek Raina

[ImkeF · Accepted solution]

Pretty close again 😉 :

=Table.AddColumn(ChgType, "xval", each try [x]-#"Changed Type1"{[Index=[Index]-1]}[x] otherwise null)

[prateekraina]

Hi @ImkeF,

Is there any other way of achieveing this? Performance wise it is taking very long to refresh the report.

Prateek Raina

[ImkeF]

Yes, that might be the case.

You can use shifted index then: Add another column where you calculate "PreviousRowIndex": [Index]-1

Then join that with itself on both indices:

Table.NestedJoin(<PreviousStepName>,{"Index"}, <PreviousStepName> ,{"PreviousRowIndex"},"NewColumn",JoinKind.LeftOuter)

Expand X and calculate difference to existing X

This screencast shows how to join a table with itself: https://www.youtube.com/watch?v=looCm3cbINw

[prateekraina]

Hi @ImkeF,

Can you please elaborate these steps as i have recently started learning M.
I have published a sample report which has all the steps i have done so far, it would be really helpful if you could edit the same and share back.
Link: https://drive.google.com/drive/folders/0B2-x82_nA30gcDhKRzFQZ1hxSTQ?usp=sharing

Thanks for your patience.

Prateek Raina

[ImkeF]

That's understandable, as we have a double-merge now 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyivNyVHSQaFidXAKGwI5RkBsik8RDmFjIMcQjEE8kClmQGyBT4sJkAPBsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [X = _t, Y = _t, Z = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"X", Int64.Type}, {"Y", Int64.Type}, {"Z", Int64.Type}}),
    RowNumber = Table.AddIndexColumn(#"Changed Type", "RowNumber", 0, 1),
    #"Filtered Rows" = Table.SelectRows(RowNumber, each ([X] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1),
    SecondIndex = Table.AddColumn(#"Added Index", "IndexPrevRow", each [Index]+1),
    MergeForPrevRow = Table.NestedJoin(SecondIndex,{"Index"},SecondIndex,{"IndexPrevRow"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(MergeForPrevRow, "NewColumn", {"X"}, {"X.1"}),
    DiffX = Table.AddColumn(#"Expanded NewColumn1", "DiffX", each [X]-[X.1]),
    MergeBackToMainTable = Table.NestedJoin(RowNumber,{"RowNumber"},DiffX,{"RowNumber"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(MergeBackToMainTable, "NewColumn", {"Index", "DiffX"}, {"Index", "DiffX"})
in
    #"Expanded NewColumn"

Pls let me know if I have to upload the file as well.

[prateekraina]

Hi @ImkeF,

Thanks for the explanation, will surely try it out tomorrow.

Just adding a step further to this problem (which for sure is the last step 😄 ), can you tell me if i have additional column (SensorKey) as depicted in table below and i want to reiterate this logic once the key changes, what logic should be added.

Sensor Key	X	Y	Z	Xval
1	null	null	null	null
1	1	2	5	null
1	null	null	null	null
1	3	1	1	2
1	2	6	8	-1
1	null	null	null	null
1	4	4	4	2
2	null	null	null	null
2	1	2	5	null
2	null	null	null	null
2	3	1	1	2
2	2	6	8	-1
2	null	null	null	null
2	4	4	4	2

Prateek Raina

[ImkeF]

Yes, thats easy. Will post solution tomorrow.

[prateekraina]

Great!! 🙂 Will look forward to it. Thanks a bunch 🙂

[ImkeF]

You "partition" your table into one table per SensorKey and apply the logic that we have so far. In order to do so, you transform the query into a function.

This is the full query code with sample data:

let

Function = (Table) =>
let
    RowNumber = Table.AddIndexColumn(Table, "RowNumber", 0, 1),
    #"Filtered Rows" = Table.SelectRows(RowNumber, each ([X] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1),
    SecondIndex = Table.AddColumn(#"Added Index", "IndexPrevRow", each [Index]+1),
    MergeForPrevRow = Table.NestedJoin(SecondIndex,{"Index"},SecondIndex,{"IndexPrevRow"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(MergeForPrevRow, "NewColumn", {"X"}, {"X.1"}),
    DiffX = Table.AddColumn(#"Expanded NewColumn1", "DiffX", each [X]-[X.1]),
    MergeBackToMainTable = Table.NestedJoin(RowNumber,{"RowNumber"},DiffX,{"RowNumber"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(MergeBackToMainTable, "NewColumn", {"Index", "DiffX"}, {"Index", "DiffX"})
in
    #"Expanded NewColumn",

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUcorzcnBTsXqQFSAsBEQm6JLENZqDNUOMQImCjLMDIgtgFjXkATjTJAwxDgjgvqMcPmAOK2YPjDC7gPijEPzQSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sensor Key" = _t, X = _t, Y = _t, Z = _t, Xval = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sensor Key", Int64.Type}, {"X", Int64.Type}, {"Y", Int64.Type}, {"Z", Int64.Type}, {"Xval", Int64.Type}}),
    PartitionTable = Table.Group(#"Changed Type", {"Sensor Key"}, {{"Partition", each _, type table}}),
    CallFunctionForEachTable = Table.AddColumn(PartitionTable, "TransformedTable", each Function([Partition])),
    Cleanup = Table.RemoveColumns(CallFunctionForEachTable,{"Partition"}),
    Expand = Table.ExpandTableColumn(Cleanup, "TransformedTable", {"X", "Y", "Z", "Xval", "RowNumber", "Index", "DiffX"}, {"X", "Y", "Z", "Xval", "RowNumber", "Index", "DiffX"})
in
    Expand

and here the file to play around: https://1drv.ms/u/s!Av_aAl3fXRbehasMiQjFM0wV8VHV8A

[prateekraina]

Thanks for all the help @ImkeF. Really appreciate it!!

Prateek Raina

[prateekraina]

Thanks a lot @ImkeF. You are superb !!

Prateek Raina