Pick the first occurrence then step by number (n)

[samahiji]

Hi,

I have a table of X and Y values, where either X or Y increases by a step of n. I need Power Query to first identify which column (X or Y) has the increasing trend by n. Then, apply a formula to the identified column by selecting the first occurrence after each step of n.

[Omid_Motamedise · Accepted solution]

Hi @samahiji 

Just copy and past the below formula in advance editor

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jU0MlbSUTIEYiOlWB1kMRA2QxOzAGJzNDFDI7AEmqAZFpVGIGtM0QRNQKoN4YImpmZQu4GyqILmIEFTbIKWaIIgZxoboAkaGoBsA/ooFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ABC = _t, X = _t, Y = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC", type text}, {"X", Int64.Type}, {"Y", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ABC", "X", "Y"}, {},0,(a,b)=> Number.From(b[X]-a[X]>=5 or b[Y]-a[Y]>=5))
in
#"Grouped Rows"