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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
stribor45
Post Prodigy
Post Prodigy

Get data from column from unrelated tables

Is there a way to get data from table B based on match between columns from A and B even thought tables are unrelated.  Result is in table C.  Is there are duplicates in table B ignore them and take value from "Letter" on the first encounter.

 

 

stribor45_0-1741224928671.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table by DAX.

 

Jihwan_Kim_0-1741233607060.png

 

Jihwan_Kim_1-1741233738592.png

 

TABLE C = 
    ADDCOLUMNS (
        'Table A',
        "@letter",
            MINX (
                FILTER (
                    'Table B',
                    'Table B'[id] = 'Table A'[id]
                        && 'Table B'[code] = 'Table A'[code]
                ),
                'Table B'[letter]
            )
    )

 

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

8 REPLIES 8
v-hashadapu
Community Support
Community Support

Hi @stribor45 , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

techies
Solution Sage
Solution Sage

Hi @stribor45 please try this 

transform> add column> custom column

 

let
matchedRow = Table.SelectRows(#"Table b", (row) => row[id] = _[id] and row[code] = _[code])
in
if Table.RowCount(matchedRow) > 0 then matchedRow[letter]{0} else null

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978

I was more hoping to use DAX as I was aware on how to do this in PQ. Also I was hoping for the table to look like this as I want to take only some columns from table A. what if table had 4 or more columns (example below) and we wanted to use only first three columns in this calculation to get same output. Is there a way for this whole code to get wrapped up with selectcolumns?

idcodenameDay
111222JohnMonday
111333JohnTuesday
333222JimFriday
444333ElaineMonday



stribor45
Post Prodigy
Post Prodigy

Would you mind explaining what parts of this code executes first please. I am just trying to understand how this works. Does the table first gets filtered based on these conditions? 

Hi,

I tried to add comments into the code like below.

TABLE C =
    ADDCOLUMNS (
        'Table A',
        // Use the ADDCOLUMNS function to create a new calculated column within 'Table A'
        "@letter",
            // Name the calculated column as "@letter"
            // Define the expression for the "@letter" column
            MINX (
                // Use the MINX function to return the minimum value of the expression specified
                // This ensures we get the smallest (alphabetically or numerically) 'letter' value
                FILTER (
                    // Use the FILTER function to create a subset of 'Table B'
                    // The subset includes rows where both conditions below are true:
                    'Table B',
                    'Table B'[id] = 'Table A'[id] // Condition 1: Match the 'id' in 'Table B' with the 'id' in 'Table A'
                        && 'Table B'[code] = 'Table A'[code] // Condition 2: Match the 'code' in 'Table B' with the 'code' in 'Table A'
                ),
                // The column from 'Table B' to evaluate: 'Table B'[letter]
                // This is the column from which we want to extract the minimum value
                'Table B'[letter]
            )
    )

 

 

Jihwan_Kim_0-1741319033300.png

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Thank you!!!! 

If first table A had 3 more columns is there a way to somoehow only take first three (id, code and name) and then do same calculations as before?

Hi,

It will be very helpful if I can see your sample pbix file.

Please provide your sample pbix file's link, and then I can try to look into it.

Thank you.


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table by DAX.

 

Jihwan_Kim_0-1741233607060.png

 

Jihwan_Kim_1-1741233738592.png

 

TABLE C = 
    ADDCOLUMNS (
        'Table A',
        "@letter",
            MINX (
                FILTER (
                    'Table B',
                    'Table B'[id] = 'Table A'[id]
                        && 'Table B'[code] = 'Table A'[code]
                ),
                'Table B'[letter]
            )
    )

 

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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