Combining 2 tables both have duplicate values, need help!

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