Combining 2 tables both have duplicate values, need help!

[SAM_130031]

       Here is my simplified problem: both the Cost table and the Order table have duplicate values. I want to combine these two into one table, which shows all the values like the Desire result table, any value that the Order has but the Costs don't have can be blank, and it can show multiple costs of the item of the Cost table (item G). I tried Outer left but everything just doubled up and i can't find the similar problem online. I'm not very good with Excel and Power Query. I really appreciate your attention and support, this helps me a lot. Thank you very much.

[ronrsnfld]

And here is another method that seems to handle entries that are in one table but not the other, with or without data for the respective entries:

let
    CostTbl = Table.Group(Cost, {"Item"}, {
        {"Costs", each Table.AddIndexColumn(_,"Index",0,1),
        type table [Item=nullable text, Cost=Int64.Type, Index=Int64.Type]}}),
    #"Rename Cost Item" = Table.RenameColumns(CostTbl,{"Item","CostItem"}),
    OrderTbl = Table.Group(Order, {"Item"}, {
        {"Orders", each Table.AddIndexColumn(_,"Index",0), 
        type table [Item=nullable text, Units=Int64.Type, Index=Int64.Type]}}),
   
    Join = Table.Join(#"Rename Cost Item","CostItem",OrderTbl,"Item",JoinKind.FullOuter), 
    #"Removed Columns" = Table.RemoveColumns(Join,{"CostItem", "Item"}),

//blank table used in next step
x=#table({"Units","Orders","Cost"},{{null,null,null}}),

    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Cost+Orders", 
        each Table.Combine({[Costs]??x,[Orders]??x}), type table),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Costs", "Orders"}),
    
    #"Group Tables" = Table.TransformColumns(#"Removed Columns1",{"Cost+Orders", 
            each Table.Group(_,"Index",{
                {"Item", (t)=>List.RemoveNulls(t[Item]){0}?},
                {"Units", (t)=>List.RemoveNulls(t[Units]){0}?},
                {"Cost", (t)=>List.RemoveNulls(t[Cost]){0}?}               
                }), type table[Item=text,Cost=Int64.Type,Units=Int64.Type]}),

    #"Expanded Cost+Orders" = Table.ExpandTableColumn(#"Group Tables", "Cost+Orders", {"Item", "Units", "Cost"}),
    #"Removed Blank Rows" = 
        Table.SelectRows(#"Expanded Cost+Orders", each 
            not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

[SAM_130031]

Hi Ron, thank you for your reply. Can I get your PBIX or Power Query file with steps so that I can follow. I'm not very good with code. Sorry for your inconvenience, I really appreciate your help.

[ronrsnfld · Accepted solution]

Attached is the Excel File as I set it up with the Power Query

[ronrsnfld]

It'll take me a while to separate it out. It is on an Excel Sheet that has lots of other stuff in the workbook.

But here are some steps to follow.

Make sure your first table is named Cost.

Make sure your second table is named Order.

Select `Data => from Table` for each of those two tables.

 After the PQ UI opens, select `Close and Load to`  ==> `Connection Only`.

Then return to the PQ Editor and select a New Query with Source = Blank.

Go to the Advanced Editor and paste the code into the Advanced Editor, replacing everything that might be there.

[SAM_130031]

Took a while, but i managed to add some columns for my report, worked great. I learned a lot, thank you, you guys helped me a ton—my sincere appreciation to everyone for their help.

[PijushRoy]

Hi @SAM_130031 

Please find the Merge Query and attached PBIX file
Please find the M Code and logic, and let me know 

[PhilipTreacy]

Hi @SAM_130031 

Download example PBIX file with the code below 

Try this, here's the full code

let
    Source = Table.NestedJoin(Cost, {"Item"}, Order, {"Order"}, "Order", JoinKind.FullOuter),
    #"Expanded Order" = Table.ExpandTableColumn(Source, "Order", {"All"}, {"All.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Order", "Costs", each [All][Cost]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Units", each [All.1][Units]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom1", "Units.1", each if List.Count([Units]) < List.Count([Costs]) then List.InsertRange([Units], List.Count([Units]), List.Repeat({0}, List.Count([Costs]) - List.Count([Units]))) else [Units]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom3", "Custom", each List.Zip({[Units.1],[Costs]})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"All", "All.1", "Costs", "Units", "Units.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Units"}, {"Custom.2", "Costs"}})
in
    #"Renamed Columns"

I did a full outer merge and then had to mess around with the resulting lists from the Units and Costs columns.  The trickiest part was if there was no Unit but there was a cost - for example with G.  I had to introduce a 0 for the Units in order to be able to get the right outcome.

Regards

Phil

[SAM_130031]

Herer is the shot of the error i got when tried for original data

Hi Phil, the solution you provided worked great. I have another question, suppose this time the Order table have extra item but the Cost table still the same. I tried your solution but at the added custom column Costs, it has error value. And if the other way around with the Cost table has extra item but the Order dont't have then what solution will be? Because the data of these 2 table i will feed them more data. Thank you very much for your fast response, it was really helpful.