Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:)
Solved! Go to 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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.
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIMHO, 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
Ask yourself if you really have to do this in Power Query or if this can be done at the data source?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.