Forum Discussion

SamWhite's avatar
SamWhite
Frequent Visitor
2 years ago

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?

Need Help

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    NestedJoin the two tables based on the "Item" columns

    Extract the appropriate price from the joined column

    let
    
//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(
        {{"FirstSource","SecondSource","FirstSource","SecondSource","SecondSource"},
         {"Laptop","Laptop","SmartPhone","SmartPhone","WashingMachine"},
         {500,700,450,600,350}
        },
        type table[Source=text,  Item=text, Price=Currency.Type]),
    
    Items=Table.FromColumns(
        {{"Laptop","SmartPhone","WashingMachine"}},
        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(
                        Joined, 
                        "Price", 
                        each try [joined]{List.PositionOf([joined][FirstSource])} 
                                otherwise [joined][Price]{0},
                      Currency.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"joined"})
in
    #"Removed Columns"