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

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

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

v-heq-msft
Community Support
Community Support

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
v-heq-msft
Community Support
Community Support

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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