Merge two rows to complete values dropped below row

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"