Merging specific rows within a table

[db_huk]

Hi There, 

I have a table listing vendor numbers and names and their corresponding performance sales, margin, SKUs etc. Recently a number of new vendor numbers were created therefore we have 2 vendor accounts for 1 vendor. Is there a way I can merge the old data with the new data for a specific list of vendor accounts where this has happened?

I dont know whether theres a way I can do it automatically by providing a sheet of vendor accounts that are duplicates? or whether I would have to do it manually for those specific vendor accounts. 

I can share part of the data if that is needed but here is a screenshot of an example of the data where there are 2 different vendor numbers and I need to sum the sales, etc into the new vendor number which is the 9000 number inheriting the new country column as well 

[v-karpurapud]

Hi @db_huk 
Hope you are doing well!

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @db_huk 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @db_huk 
Hope you are doing well!

Thank you @dufoq3 for the prompt response on this topic.

Could you please confirm if your query have been resolved the solution provided by @dufoq3 If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[dufoq3 · Accepted solution]

Hi @db_huk, check this:

Before

After

1.) you need to specify columns to SUM here (all other columns will be used from NEW Vendor):

2.) You have to add all Vendor Pairs here by clicking on a gear icon:

3.) If you do not need same rows order, delete SortedRows step because it slows the query if you have a lot of rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BCsIwEER/JeRcwsYklfyAIEJ7KHopPQQSbDBNoBbFv3c3eKmXmdnZ5bHjyKVUije8T57dQvZlZZ1bAjbXHLfg2SXmuy8LFif3nGPJmI6AYq2wFt2AUIbmFkUDn5qRWwA66cL7D3reXPrsWITQWkhCHLQAia5IWllRxtCm3+awsgF/SeGHxHZI5eUe0e2AEqpAfY1iJRs+TV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Vendor Number" = _t, #"Vendor Name" = _t, Country = _t, Division = _t, #"Sales Qty" = _t, Sales = _t, Margin = _t, #"GR Qty" = _t, SKUs = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Vendor Number", Int64.Type}, {"Sales Qty", type number}, {"Sales", type number}, {"Margin", type number}, {"GR Qty", type number}, {"SKUs", type number}}, "en-US"),
    ColumnsToSum = {"Sales Qty", "Sales", "Margin", "GR Qty"},
    VendorsOldNew = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0NlbSUbI0MDBQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Old = _t, New = _t]),
    ChangedType2 = Table.TransformColumnTypes(VendorsOldNew,{{"Old", Int64.Type}, {"New", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(ChangedType2, "Index", 0, 1, Int64.Type),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"Index"}, "Type", "VendorNo"),
    StepBack = ChangedType,
    AddedIndex2 = Table.AddIndexColumn(StepBack, "Index2", 0, 1, Int64.Type),
    MergedQueries = Table.NestedJoin(AddedIndex2, {"Vendor Number"}, UnpivotedOtherColumns, {"VendorNo"}, "H", JoinKind.LeftOuter),
    ExpandedH = Table.ExpandTableColumn(MergedQueries, "H", {"Index", "Type"}, {"Index", "Type"}),
    GroupedRows = Table.Group(ExpandedH, {"Index"}, {{"All", each _}, {"T", each
        if not List.Contains({"Old", "New"}, _{0}[Type]) then _
        else Table.TransformColumns(Table.SelectRows(_, (x)=> x[Type] = "New"), List.Transform(ColumnsToSum, (r)=> {r, (y)=> List.Sum(Table.Column(_, r)) })) , type table }}),
    CombinedT = Table.Combine(GroupedRows[T]),
    SortedRows = Table.SelectColumns(Table.Sort(CombinedT,{{"Index2", Order.Ascending}}), Table.ColumnNames(ChangedType)),
    ChangedType3 = Value.ReplaceType(SortedRows, Value.Type(Table.FirstN(ChangedType, 0)))
in
    ChangedType3