Forum Discussion
Compare Any 2 non-consecutive Rows (PowerQuery)
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:)
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"
6 Replies
- lbendlin
Super User
Ask yourself if you really have to do this in Power Query or if this can be done at the data source?
CNENFRNLCommunity Champion
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
Impactful Individual
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.
- 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.
