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

 

 

Hi SAM_130031 

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

 

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"