Forum Discussion
Search then pull column data into another table
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 | ||
| 5x | A | 5/24/2025 | 4/24/2025 | ||
| 6x | C | 5/25/2025 | 4/25/2025 | ||
| 7x | C | 5/26/2025 | 4/26/2025 |
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.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())
9 Replies
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] )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.
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
Jihwan_Kim 's solution will do the magic if you want to get matched value of category and ID between two table.
Thank you.
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.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.
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.
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.
