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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Join two excel files bases on condition

I have a excel file like below, where I have price of items from different sources. 




However some items only have data from SecondSource. Also, FirstSource price is more reliable than the SecondSource.

I have another excel file which only has the items as below:




I want to join the file having only the items with the first excel file shown here, and if the Item is present both in FirstSource and SecondSource, I only want to return the Price from the FirstSource and for items which have only data from SecondSource, I want to return the price from the second source as shown below:




How can I accomplish this using power query Merge operation?

Super User
Super User

NestedJoin the two tables based on the "Item" columns

Extract the appropriate price from the joined column

//create the data for this example
//  note I removed spaces in Items so it would match with the
//  contents of the "Items" table
// I am assuming a typo in your question.
    Prices = Table.FromColumns(
        type table[Source=text,  Item=text, Price=Currency.Type]),
        type table[Item=text]),

//Join the two tables
    Joined = Table.NestedJoin(Items,"Item",Prices,"Item", "joined"),

//Extract FirstSource if present, else extract what's there
//  note absence of specific check for SecondSource
    #"Added Custom" = Table.AddColumn(
                        each try [joined]{List.PositionOf([joined][FirstSource])} 
                                otherwise [joined][Price]{0},
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"joined"})
    #"Removed Columns"
Super User
Super User

Hi @SamWhite ,
create a lookup table with the relevant prices only (disable load to data model) by referencing the first table
-> sort by Source
-> Use Table.Buffer around it to ensure the sort order sticks (Bug warning for Table.Sort and removing duplicates... - Microsoft Fabric Community)
-> check column "Item" and remove duplicates
-> go to the second table and merge with this new table to retrieve desired prices.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors