Search then pull column data into another table

[Earl40]

Hi I need some help, please let me know if i need to clarify my reuqest. Thank you im advamce I have 2 data sources connected. I have table A, and I want to bring in Order Date and source info from table B into tabke A but ONLY for items in Category columumn A and B ONLY if the  values Order ID # Category Create Date match the same in table B

Table A

ID #	Category	Create Date
1x	A	5/20/2025
2x	B	5/21/2025
3x	B	5/22/2025
4x	A	5/23/2025
5x	A	5/24/2025
6x	C	5/25/2025
7x	C	5/26/2025

Table B

ID #	Order #	Category	Create Date	Order Date	Source
1x		A	5/20/2025	4/20/2025	Paper
2x		B	5/21/2025	4/21/2025	Paper
3x		B	5/22/2025	4/22/2025	Paper
4x		A	5/23/2025	4/23/2025	Email
5x		A	5/24/2025	4/24/2025	Email
6x		C	5/25/2025	4/25/2025	Email
7x		C	5/26/2025	4/26/2025	Email

[v-ssriganesh · Accepted solution]

Hello @Earl40,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop based on the details you shared and I got the expected result as per your requirements.

You wanted to bring in Order Date and Source from Table B into Table A, only if:

• ID #, Category, and Create Date match between both tables
• AND the Category is either A or B

What I Did Practically in Power BI:

• Filtered Table A to include only Category A and B
• Performed a Merge (Inner Join) between Table A and Table B using three matching fields: ID #, Category, and Create Date
• Expanded the required columns (Order Date and Source) from Table B into Table A

Expected Output:

For your reference, I’ve attached the .pbix file that demonstrates the solution using your sample data. Please feel free to download and explore it to apply the same logic in your model.

Best Regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @Earl40, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @Earl40,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Earl40,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @Earl40,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop based on the details you shared and I got the expected result as per your requirements.

You wanted to bring in Order Date and Source from Table B into Table A, only if:

• ID #, Category, and Create Date match between both tables
• AND the Category is either A or B

What I Did Practically in Power BI:

• Filtered Table A to include only Category A and B
• Performed a Merge (Inner Join) between Table A and Table B using three matching fields: ID #, Category, and Create Date
• Expanded the required columns (Order Date and Source) from Table B into Table A

Expected Output:

For your reference, I’ve attached the .pbix file that demonstrates the solution using your sample data. Please feel free to download and explore it to apply the same logic in your model.

Best Regards,
Ganesh Singamshetty.

[Ashish_Mathur]

Hi,

Write this calculated column formula in TableA

=if(TableA[Category]="A"||TableA[Category]="B",CALCULATE(MAX(TableB[Order Date]),FILTER(TableB,TableB[Category]=EARLIER(TableA[Category])&&TableB[Create Date]=EARLIER(TableA[Create Date]))),BLANK())

Hope this helps.

[Earl40]

@Ashish_Mathur Thank you. I tried the formula and all values came out blank. it turns out I have a data mismatch in the date columns that wont be resolved. If I want to alter the formula to  just want to match the Category and ID numbers, how would that be done? thank you in advance

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=if(TableA[Category]="A"||TableA[Category]="B",CALCULATE(MAX(TableB[Order Date]),FILTER(TableB,TableB[Category]=EARLIER(TableA[Category])&&TableB[ID]=EARLIER(TableA[ID]))),BLANK())

[Irwan]

@Earl40 

@Jihwan_Kim 's solution will do the magic if you want to get matched value of category and ID between two table.

Thank you.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file if it suits your requirement. It is for creating a calculated table for each information from Table B.

CC Order date from Table B = 
SUMMARIZE (
    FILTER (
        'Table B',
        'Table B'[ID #] = 'Table A'[ID #]
            && 'Table B'[Category] = 'Table A'[Category]
    ),
    'Table B'[Order Date]
)

CC Source from Table B = 
SUMMARIZE (
    FILTER (
        'Table B',
        'Table B'[ID #] = 'Table A'[ID #]
            && 'Table B'[Category] = 'Table A'[Category]
    ),
    'Table B'[Source]
)