Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Dexter1088
Frequent Visitor

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!


 

 

 

Dexter1088_0-1722961166442.png

 

2 ACCEPTED SOLUTIONS

Hi @Dexter1088, if you have that many rows do not use Power Query for this purpose. 

 

1.) Create relationship between tables

dufoq3_0-1722969790767.png

 

2.) Create a calculated column

dufoq3_2-1722969712116.png

 

Lowest Count = 
VAR _currentID = [ID]

VAR _test1 = 
    CALCULATE(
        MIN(Table2[COUNT]),
        Table2[ID] = _currentID
    )
        
        
RETURN _test1

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Anonymous
Not applicable

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

vheqmsft_0-1723012968150.png

 

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

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

vheqmsft_0-1723012968150.png

 

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

 

Anonymous
Not applicable

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

--Nate

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

dufoq3_0-1722969790767.png

 

2.) Create a calculated column

dufoq3_2-1722969712116.png

 

Lowest Count = 
VAR _currentID = [ID]

VAR _test1 = 
    CALCULATE(
        MIN(Table2[COUNT]),
        Table2[ID] = _currentID
    )
        
        
RETURN _test1

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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!

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors