Skip to main content
cancel
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

Reply
SamWhite
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. 

SamWhite_0-1695126220876.png

 

 

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:

SamWhite_1-1695126264993.png

 

 

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:

SamWhite_2-1695126297101.png

 

 

How can I accomplish this using power query Merge operation?

2 REPLIES 2
ronrsnfld
Super User
Super User

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"
ImkeF
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

Announcements
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