Merge two rows to complete values dropped below row

[Mekan]

I am having a trouble merging two rows. Basically I have a list of auto parts. The first column is part ID, second part description, then vendor name. Our ERP system exports data into excel not in clean way.  It shifts or drops part or vendor names which are longer than 20 symbols into below row/cell. It does it in random order, so fill up into new column and merging them did not work here. I am looking for any formula or methods to merge values shifted to below cells in random orders. I would appreciate any hep with this. Thank you very much! 

Example: 

#  I  Part ID I   Part Name                     I  Vendor name 

------------------------------------------------------------- 

 1. I 1234   I   SLEEVE 3/8" COMPRESS I AUTO TRADE PRODUCTS 

 2. I            I   ION                                 I LLC

[Mekan]

Yes it works! Thank you very much for your effort! Supper work! 

[v-henryk-mstf]

Hi @Mekan ,

I think the program @smpa01  provided has value and can be used as a reference. If the problem persists, please provide details of the error and the desired outcome. Waiting for your feedback.

Best Regards,
Henry

[smpa01]

@Mekan  did you try the solution provided?

[Mekan]

Thanks for the solution. So far no luck, to be honest my programming skills are at the begginer to intermediate stage, so I need a time to get my head around your solution. It looks quite comprehensive. If it works out then it should help a lot our headaches. Thanks for the input! 

[smpa01 · Accepted solution]

@Mekan  if you have table like following

#	PartID	Name	VendorName
1	1234	com	ven
2		pre	dor
3		ss	one
4		ion
5	2345	hvac	vendor
6			two
6	3456	plumb	vendor3

and want to achieve following

PartID	FinalName	Custom
1234	compression	vendorone
2345	hvac	vendortwo
3456	plumb	vendor3

This is how you can

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "PYzdCoAgFIPf5Vx740+9jHjRj1CQHrGy128mCWMbH2PWkiRBUmmDWDjAi4/khCWFDqXs4SvnD+oGzxPG0X/MNLZzrKWSAQWXNbYyLe30vxjbHLoe7gTrGum4w9z3mpx7AQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"#" = _t, PartID = _t, Name = _t, VendorName = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"#", Int64.Type}, {"PartID", Int64.Type}, {"Name", type text}, {"VendorName", type text}}
  ),
  #"Filled Down" = Table.FillDown(#"Changed Type", {"PartID"}),
  #"Removed Columns" = Table.RemoveColumns(#"Filled Down", {"#"}),
  #"Grouped Rows" = Table.Group(
    #"Removed Columns",
    {"PartID"},
    {
      {
        "ad",
        each _,
        type table [PartID = nullable number, Name = nullable text, VendorName = nullable text]
      }
    }
  ),
  #"Added Custom" = Table.AddColumn(
    #"Grouped Rows",
    "FinalName",
    each
      let
        x = [ad],
        Name = x[Name],
        Count = List.Count(Name),
        Final =
          if Count >= 1 then
            List.Generate(
              () => [i = 0, j = Name{i}, k = j],
              each [i] < List.Count(Name),
              each [i = [i] + 1, j = Name{i}, k = [k] & Text.From(j)],
              each [k]
            )
          else
            Text.From(Name)
      in
        Final{List.Count(Name) - 1}
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Custom",
    each
      let
        x = [ad],
        Name = x[VendorName],
        Count = List.Count(Name),
        Final =
          if Count >= 1 then
            List.Generate(
              () => [i = 0, j = Name{i}, k = j],
              each [i] < List.Count(Name),
              each [i = [i] + 1, j = Name{i}, k = [k] & Text.From(j)],
              each [k]
            )
          else
            Text.From(Name)
      in
        Final{List.Count(Name) - 1}
  ),
  #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1", {"ad"})
in
  #"Removed Columns1"