Power Query Reference another table and get MIN value

[Dexter1088]

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!

[dufoq3 · Accepted solution]

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

[v-heq-msft · Accepted solution]

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 He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[watkinnc]

Merge the tables on ID, then instead of expanding the tables, choose Aggregate-Max. 

--Nate

[Dexter1088]

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!

[dufoq3 · Accepted solution]

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

[Dexter1088]

This seems to work better, but for the First Variable 

_currentID

It will only Let me use measures or exact text here. How could I link this to 'Table1'[ID]? Thanks!

[dufoq3]

You have to put my code into CALCULATED COLUMN, not in a new measure.