I changed this code up a bit to keep the order of the columns in the same order as you have provided in the Asset Table. This will eliminate any "hard coding" or movement of columns

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitColumn = Table.SplitColumn(T1, "Asset", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Asset.1", "Asset.2"}),
    ReplaceV = Table.ReplaceValue(SplitColumn,".","",Replacer.ReplaceText,{"Asset.1"}),
    RenameC = Table.RenameColumns(ReplaceV,{{"Asset.2", "Asset"}}),
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SplitColumn2 = Table.SplitColumn(T2, "Unit", Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.Csv, false), {"Unit.1", "Unit.2"}),
    RenameC2= Table.RenameColumns(SplitColumn2,{{"Unit.1", "No."}}),
    Insert = Table.AddColumn(RenameC2, "Text Between Delimiters", each Text.BetweenDelimiters([Unit.2], "(", ")"), type text),
    SplitC3 = Table.SplitColumn(Insert, "Unit.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Unit.2.1", "Unit.2.2", "Unit.2.3"}),
    RemoveColumn= Table.RemoveColumns(SplitC3,{"Unit.2.1", "Unit.2.3"}),
    RenameC3 = Table.RenameColumns(RemoveColumn,{{"Unit.2.2", "Unit"}}),
    SplitColumn3 = Table.ExpandListColumn(Table.TransformColumns(RenameC3, {{"Text Between Delimiters", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text Between Delimiters"),
    RenameC4 = Table.RenameColumns(SplitColumn3,{{"Text Between Delimiters", "Asset Number"}}),
    MQ = Table.NestedJoin(RenameC4, {"Asset Number"},RenameC, {"Asset.1"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Asset"}, {"Asset"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each "x"),
    #"Sorted Rows2" = Table.Sort(#"Added Custom",{{"Asset Number", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows2",{"Asset Number"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Asset]), "Asset", "Custom"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"No.", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"No.", Order.Ascending}})

in
   #"Sorted Rows"