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.
Hello,
Can someone help me with the follow analyse?
I need to find matching rows, the conditions are the follow.
Date > equal
Product > equal
Receiver > equal
TAG > equal
Time > time frame of 2 minutes
The orange lines are matching rows. The result is the min value of the index colum.
Greets,
Ronald
Power bi file
Solved! Go to Solution.
Try this Custom Column
Please takle a look at attached file
=let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index])
Hi @Ronald123
My apologies for not getting back earlier
Try this DAX column. It works with sample data.
See if it performs better than M custom column
Calculated Column = CALCULATE ( MIN ( Test[Index] ), FILTER ( Test, [Product] = EARLIER ( [Product] ) && [Receiver] = EARLIER ( [Receiver] ) && [Date] = EARLIER ( [Date] ) && [TAG] = EARLIER ( [TAG] ) && ABS ( [TIme] - EARLIER ( [TIme] ) ) <= TIME ( 0, 2, 0 ) ) )
Try this Custom Column
=let myproduct=[Product], myreceiver=[Receiver] in List.Min(Table.SelectRows(#"Added Index",each [Product]=myproduct and [Receiver]=myreceiver)[Index])
Thx for the reply either i'm missing some calculating conditions;
Date must be equal
Tag must be equal
Time - time frame of 2 minutes
Greets,
Ronald
My bad I didn't look at your question carefully.
Just looked at the picture.
Will get back to you
Try this Custom Column
Please takle a look at attached file
=let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index])
I use the Power Query you've made in a calculation for a Google Big Query project.
The Google Big Query is created with a SQL with a max of 100.000 lines.
Either the Power Query calculation calculate over the more than 20 milion lines.
Also checked with a CSV with 100.000 lines with a total of 10 mb.
The query is running a quarter with a total of 12 GB loaded in the model.
Is there a solution for this issue?
Greets,
Ronald
Try Table.Buffer,
IN the Query Editor>>View>>advanced editor
In the step just before the step in which custom column was added, try wrapping the code inside Table.Buffer
For example in the file I shared
#"Changed Type1" = Table.Buffer(Table.TransformColumnTypes(#"Duplicated Column",{{"TIme2", type number}})), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each let myproduct=[Product], myreceiver=[Receiver],mytag=[TAG],mydate=[Date], mytime=[TIme2] in List.Min(Table.SelectRows(#"Changed Type1",each [Product]=myproduct and [Receiver]=myreceiver and [Date]=mydate and [TAG]=mytag and Number.Abs((Duration.TotalMinutes(Duration.From([TIme2]))-Duration.TotalMinutes(Duration.From(mytime))))<=2)[Index])) in #"Added Custom1"
Tanks for you're reply but table.buffer doesn't help.
In Power Query is't possible to run the calculation but is isn't possible to apply the query in the model.
On my personal computer i have memory issue's with this calculation and the server of my work is runing and runing...
Is there an another option? Maybe DAX?
Greets,
Ronald
I will look into it
Hi @Ronald123
My apologies for not getting back earlier
Try this DAX column. It works with sample data.
See if it performs better than M custom column
Calculated Column = CALCULATE ( MIN ( Test[Index] ), FILTER ( Test, [Product] = EARLIER ( [Product] ) && [Receiver] = EARLIER ( [Receiver] ) && [Date] = EARLIER ( [Date] ) && [TAG] = EARLIER ( [TAG] ) && ABS ( [TIme] - EARLIER ( [TIme] ) ) <= TIME ( 0, 2, 0 ) ) )
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.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |