Merging Queries - Assistance with Duplicates

Hi Anonymous 

It is possible to add the Cost from Table B to only the first row of the same invoice number in Table A, however, I am not sure how you can analyse data after merge the 2 tables in this way...basically, you group Table A with all data as a table, then add the number to the first row, then expand it

#"Grouped Rows" = Table.Group(your previous step, {"your inovice number column"}, {{"allrows", each _, type table}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"your inovice number column"}, TableB, {"your inovice number column"}, "TableB", JoinKind.LeftOuter),
#"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"your cost column"}, {"TableB.Cost"}),
#"Added Custom" = Table.AddColumn(#"Expanded TableB", "Custom", (OT) => [a=Table.AddIndexColumn(OT[allrows],"Index"),
b=Table.AddColumn(a,"Value",each if [Index]=0 then OT[TableB.Cost] else null)][b])

Hi, Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

TableA:

TableB:

You may apply the following transformations on 'TableA'. Here are the m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyADItEMwCQ6VYnWglI9wKjAgpMAYrMEZVYGiApMIErMIEyDTCocKUoAozsApTPCrMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Date = _t, Due = _t, #"Product Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Date", type date}, {"Due", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if 
List.First(
Table.SelectRows(#"Added Index",(x)=>x[Number]=[Number])[Index]
)=[Index]
then List.Sum(Table.SelectRows(#"Table B",(y)=>y[Number]=[Number])[Log Cost])
else 
null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.