Compare Any 2 non-consecutive Rows (PowerQuery)

[Anonymous]

Hi Guys,

I need to compare the Count values by Sector at every Time interval (e.g. row at Index 2 with row at Index 7) and output the result in Count.1 column.

- These Rows may be at different Index distances at every run (depending on sector's data received).

- The Sector values itself may vary.

- After the first 1-2 rows (always 0 Count value) the Time interval is constant every 10s.

I cannot play with custom Index columns given the requirements mentioned above, so I came up with the below solution (Count.1 values in the Table below are the correct output):

#"Added Count.1" = Table.AddColumn(#"Previous step", "Count.1", (a) =>

  let   

    b = Table.SelectRows(#"Previous step", (x) => x[Sector] = a[Sector] and x[Time] = a[Time]+10)  

  in

    b[#"Count"]{0} 

  )

But this is veeery slow, and I'm using test data (300 rows), when the report will be live the number of rows will grow exponentially. Any idea how to improve my code to a more efficient/fast way?

Sample data:

Index	Time	Sector	Count	Count.1
0	1593018439	200	0	0
1	1593018440	200	0	0
2	1593018450	4	198	208
3	1593018450	5	176	214
4	1593018450	6	176	180
5	1593018450	3	176	180
6	1593018450	1	178	216
7	1593018460	4	208	208
8	1593018460	5	214	220
9	1593018460	6	180	180
10	1593018460	3	180	180
11	1593018460	1	216	220
12	1593018470	1	220	220
13	1593018470	6	180	180
14	1593018470	3	180	180
15	1593018470	4	208	208
16	1593018470	5	220	220
17	1593018480	4	208	208
18	1593018480	5	220	220
19	1593018480	6	180	180
20	1593018480	3	180	180
21	1593018480	1	220	220

Thanks:)

[ziying35]

@Anonymous 

Right, don't make duplicate posts, just one post for each question. I didn't notice you made a duplicate post, mark the post as solved or delete it.

[edhans]

@Anonymous  this is a duplicate post. Can you either mark this thread as solved, or delete your original post? Thanks. Not sure if you do that if @ziying35 @CNENFRNL  and @lbendlin  have to delete theirs too or as the OP you delete the whole thread....

Original and solved post here.

[Anonymous]

@edhans  @ziying35 

Sorry guys, I didn't realize it had been published twice, the first time didn't go through (it didn't appear anywhere) so I re-wrote it.

I'm not able to delete this post now so I marked it as solved.

[CNENFRNL · Accepted solution]

IMHO, it's Table.SelectRows that slows things down. I rewrite the logic with Table.Group; it turns out to be a bit faster now. (I tested a mockup of 1,500 rows; loading time is around 7~8s)

I'm also looking forward to gurus' better solutions.

Mine is for your reference,

let
Source = Excel.CurrentWorkbook(){[Name="Log"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Time", Int64.Type}, {"Sector", Int64.Type}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Count.1", each
[sub1 = Table.Group(#"Changed Type",{"Sector"},{{"Count", each _}}),
sub2 = sub1{[Sector=[Sector]]}[Count],
sub3 = sub2{[Time=[Time]+10]}[Count]][sub3])
in
#"Added Custom"

[ziying35]

Hi, @CNENFRNL 

Yes, the Table.SelectRows function is one of the less efficient functions ir Power Query, but sometimes you have to use it to process data.

Can you clear out the sensitive information inside the form, upload the file to the cloud drive, and share the link here?l'd like to see if there's a way to optimize the code based on the actual amount of data.

[lbendlin]

Ask yourself if you really have to do this in Power Query or if this can be done at the data source?