Forum Discussion
Power Query Reference another table and get MIN value
Hi all!
I'm having zero luck tryinng to make this a measure, but now I'm thinking it would be better to just do this in query.
Really basic, but i'm drawing a blank. I have 2 Tables and need Table 1 to find the Same ID in table 2 with the lowest count and refrence it.
Below is a screenshot of a example, the Blue is what I'm trying to add in Table 1. Let me know if you need more info, thanks!
Hi Dexter1088, if you have that many rows do not use Power Query for this purpose.
1.) Create relationship between tables
2.) Create a calculated column
Lowest Count = VAR _currentID = [ID] VAR _test1 = CALCULATE( MIN(Table2[COUNT]), Table2[ID] = _currentID ) RETURN _test1Hi Dexter1088 ,
As Super User Dufoq3 said, using power query to manipulate millions of rows of data can be really slow. If you just want to display the data there is an easier way, as Super User Dufoq3 shows, to create the relationship and then use Table visualization to combine the ID column from Table 1 with the Value column from Table 2 and aggregate the Value columns, so that you don't need to perform the Calculation
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
6 Replies
Working on that now, Been waiting 10 min and it's still going. Table 2 is Massive, over a milion. Is there any way to speed it up? thanks!
Hi Dexter1088, if you have that many rows do not use Power Query for this purpose.
1.) Create relationship between tables
2.) Create a calculated column
Lowest Count = VAR _currentID = [ID] VAR _test1 = CALCULATE( MIN(Table2[COUNT]), Table2[ID] = _currentID ) RETURN _test1
Hi Dexter1088 ,
As Super User Dufoq3 said, using power query to manipulate millions of rows of data can be really slow. If you just want to display the data there is an easier way, as Super User Dufoq3 shows, to create the relationship and then use Table visualization to combine the ID column from Table 1 with the Value column from Table 2 and aggregate the Value columns, so that you don't need to perform the Calculation
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
