Apologies for the late response; query below:
let
Source = Excel.Workbook(File.Contents("B:\BP\Work Exchange\Export\2017.08.10_SC.xlsx"), null, true),
SHARECAT = Source{[Item="2017.08.10_SC",Kind="Sheet"]}[Data],
ALL_TEXT = Table.TransformColumnTypes(SHARECAT,List.Transform(Table.ColumnNames(SHARECAT),each {_, type text})),
PROMOTE_HEADERS = Table.PromoteHeaders(ALL_TEXT, [PromoteAllScalars=true]),
TRIM_STATUS = Table.TransformColumns(PROMOTE_HEADERS,{{"Tag Status", Text.Trim, type text}}),
REMOVE_COLUMNS = Table.RemoveColumns(TRIM_STATUS,{"ORIGINATING CONTRACTOR","SYSTEM","Service Description","TAG STATUS (OPERATIONAL / VOID)","LOOP ID","PURCHASE ORDER NO"}),
TRANSFORM_ATTR_1 = Table.RenameColumns(REMOVE_COLUMNS,Table.ToRows(TRANSFORM_ATTR), MissingField.Ignore),
TRANSFORM_ATTR_2 = Table.SelectColumns(TRANSFORM_ATTR_1,TRANSFORM_ATTR_LIST[EB ATTR],MissingField.Ignore),
FILTER_TAGS = Table.SelectRows(TRANSFORM_ATTR_2, each ([#"TAG STATUS (OPERATIONAL / VOID)"] = "Operational") and ([TAG CODE] <> "LAD")),
DUPLICATE_TAG_NUMBER = Table.DuplicateColumn(FILTER_TAGS, "TAG NUMBER", "TAG NUMBER COPY"),
SPLIT_TAG_NUMBER = Table.SplitColumn(DUPLICATE_TAG_NUMBER, "TAG NUMBER COPY", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"TAG NUMBER COPY.1", "TAG NUMBER COPY.2", "TAG NUMBER COPY.3"}),
REMOVE_EXTRAS = Table.RemoveColumns(SPLIT_TAG_NUMBER,{"TAG NUMBER COPY.2","TAG NUMBER COPY.3"}),
RENAME_PLID = Table.RenameColumns(REMOVE_EXTRAS,{{"TAG NUMBER COPY.1", "PROJECT LOCATION IDENTIFIER"}}),
CONCAT_DATASHEET_1 = Table.ReplaceValue(RENAME_PLID," ","_",Replacer.ReplaceText,{"PIPING SPECIFICATION DOC NO"}),
CONCAT_DATASHEET_2 = Table.ReplaceValue(CONCAT_DATASHEET_1," ","_",Replacer.ReplaceText,{"PROJECT DATASHEET DOC NO"}),
CONCAT_DATASHEET_3 = Table.CombineColumns(Table.TransformColumnTypes(CONCAT_DATASHEET_2, {{"PROJECT DATASHEET DOC NO", type text}, {"PIPING SPECIFICATION DOC NO", type text}}, "en-GB"),{"PROJECT DATASHEET DOC NO", "PIPING SPECIFICATION DOC NO"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"DATA SHEET"),
CONCAT_DATASHEET_4 = Table.ReplaceValue(CONCAT_DATASHEET_3,"NA","",Replacer.ReplaceText,{"DATA SHEET"}),
CONCAT_DATASHEET_5 = Table.ReplaceValue(CONCAT_DATASHEET_4,"N/A","",Replacer.ReplaceText,{"DATA SHEET"}),
CONCAT_DATASHEET_6 = Table.ReplaceValue(CONCAT_DATASHEET_5,"N_/_A","",Replacer.ReplaceText,{"DATA SHEET"}),
CONCAT_DATASHEET_7 = Table.TransformColumns(CONCAT_DATASHEET_6,{{"DATA SHEET", Text.Trim, type text}}),
CONCAT_DATASHEET_8 = Table.ReplaceValue(CONCAT_DATASHEET_7," ","|",Replacer.ReplaceText,{"DATA SHEET"}),
CONCAT_DATASHEET_9 = Table.ReplaceValue(CONCAT_DATASHEET_8,"_"," ",Replacer.ReplaceText,{"DATA SHEET"}),
CONCAT_BLOCK_DIAGRAM_1 = Table.ReplaceValue(CONCAT_DATASHEET_9," ","_",Replacer.ReplaceText,{"SOFTWARE FUNCTION BLOCK DIAGRAM"}),
CONCAT_BLOCK_DIAGRAM_2 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_1," ","_",Replacer.ReplaceText,{"BLOCK DIAGRAM DOC NO"}),
CONCAT_BLOCK_DIAGRAM_3 = Table.CombineColumns(Table.TransformColumnTypes(CONCAT_BLOCK_DIAGRAM_2, {{"BLOCK DIAGRAM DOC NO", type text}, {"SOFTWARE FUNCTION BLOCK DIAGRAM", type text}}, "en-GB"),{"BLOCK DIAGRAM DOC NO", "SOFTWARE FUNCTION BLOCK DIAGRAM"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"BLOCK DIAGRAM"),
CONCAT_BLOCK_DIAGRAM_4 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_3,"NA","",Replacer.ReplaceText,{"BLOCK DIAGRAM"}),
CONCAT_BLOCK_DIAGRAM_5 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_4,"N/A","",Replacer.ReplaceText,{"BLOCK DIAGRAM"}),
CONCAT_BLOCK_DIAGRAM_6 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_5,"N_/_A","",Replacer.ReplaceText,{"BLOCK DIAGRAM"}),
CONCAT_BLOCK_DIAGRAM_7 = Table.TransformColumns(CONCAT_BLOCK_DIAGRAM_6,{{"BLOCK DIAGRAM", Text.Trim, type text}}),
CONCAT_BLOCK_DIAGRAM_8 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_7," ","|",Replacer.ReplaceText,{"BLOCK DIAGRAM"}),
CONCAT_BLOCK_DIAGRAM_9 = Table.ReplaceValue(CONCAT_BLOCK_DIAGRAM_8,"_"," ",Replacer.ReplaceText,{"BLOCK DIAGRAM"}),
UNPIVOT_TABLE = Table.UnpivotOtherColumns(CONCAT_BLOCK_DIAGRAM_9, {"TAG NUMBER"}, "Attribute", "Value"),
TRANSFORM_VALUES_0 = Table.NestedJoin(UNPIVOT_TABLE,{"Attribute", "Value"},TRANSFORM_VALUES,{"Attr Name", "SC Value"},"TRANSFORM_VALUES",JoinKind.LeftOuter),
TRANSFORM_VALUES_1 = Table.ExpandTableColumn(TRANSFORM_VALUES_0, "TRANSFORM_VALUES", {"EB Value"}, {"EB Value"}),
TRANSFORM_VALUES_2 = Table.SelectRows(TRANSFORM_VALUES_1,each true),
TRANSFORM_VALUES_3 = Table.AddColumn(TRANSFORM_VALUES_2, "Revised Values", each if [EB Value]=null then [Value] else [EB Value]),
TRANSFORM_VALUES_4 = Table.TransformColumnTypes(TRANSFORM_VALUES_3,{{"Revised Values", type text}}),
TRANSFORM_VALUES_5 = Table.RemoveColumns(TRANSFORM_VALUES_4,{"Value", "EB Value"}),
TRANSFORM_VALUES_6 = Table.Distinct(TRANSFORM_VALUES_5),
TRANSFORM_UOM_1 = Table.SplitColumn(TRANSFORM_VALUES_6, "Revised Values", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Value", "UoM"}),
TRANSFORM_UOM_2 = Table.NestedJoin(TRANSFORM_UOM_1,{"Attribute", "UoM"},TRANSFORM_UOM,{"ATTR", "SC UOM"},"TRANSFORM_UOM",JoinKind.LeftOuter),
TRANSFORM_UOM_3 = Table.ExpandTableColumn(TRANSFORM_UOM_2, "TRANSFORM_UOM", {"EB UOM"}, {"EB UOM"}),
TRANSFORM_UOM_4 = Table.AddColumn(TRANSFORM_UOM_3, "Final Value", each if [EB UOM] <> null then Text.Combine({[Value],[EB UOM]}," ") else if [UoM] <> null then Text.Combine({[Value],[UoM]}," ") else [Value], type text),
TRANSFORM_UOM_5 = Table.AddColumn(TRANSFORM_UOM_4, "Trim", each Text.Trim([Final Value]), type text),
TRANSFORM_UOM_6 = Table.SelectColumns(TRANSFORM_UOM_5,{"TAG NUMBER", "Attribute", "Trim"}),
PIVOT_TABLE = Table.Pivot(TRANSFORM_UOM_6, List.Distinct(TRANSFORM_UOM_6[Attribute]), "Attribute", "Trim"),
TRANSFORM_PHYS_1 = Table.NestedJoin(PIVOT_TABLE,{"TAG NUMBER"},TRANSFORM_PHYS,{"TAG NUMBER"},"TRANSFORM_PHYS",JoinKind.LeftOuter),
TRANSFORM_PHYS_2 = Table.ExpandTableColumn(TRANSFORM_PHYS_1, "TRANSFORM_PHYS", {"EB PHYSICAL CLASS NAME"}, {"EB PHYSICAL CLASS NAME"}),
TRANSFORM_PHYS_3 = Table.AddColumn(TRANSFORM_PHYS_2, "PHYSICAL CLASS NAME MERGE", each if [EB PHYSICAL CLASS NAME]=null then [PHYSICAL CLASS NAME] else [EB PHYSICAL CLASS NAME]),
TRANSFORM_PHYS_4 = Table.RemoveColumns(TRANSFORM_PHYS_3,{"EB PHYSICAL CLASS NAME", "PHYSICAL CLASS NAME"}),
TRANSFORM_PHYS_5 = Table.RenameColumns(TRANSFORM_PHYS_4,{{"PHYSICAL CLASS NAME MERGE", "PHYSICAL CLASS NAME"}}),
TRANSFORM_FUNC_1 = Table.NestedJoin(TRANSFORM_PHYS_5,{"TAG CODE", "FUNCTIONAL CLASS NAME"},TRANSFORM_FUNC,{"TAG CODE", "SC FUNCTIONAL CLASS"},"TRANSFORM_FUNC",JoinKind.LeftOuter),
TRANSFORM_FUNC_2 = Table.ExpandTableColumn(TRANSFORM_FUNC_1, "TRANSFORM_FUNC", {"EB FUNCTIONAL CLASS"}, {"EB FUNCTIONAL CLASS NAME"}),
TRANSFORM_FUNC_3 = Table.AddColumn(TRANSFORM_FUNC_2, "FUNCTIONAL CLASS NAME MERGE", each if [EB FUNCTIONAL CLASS NAME]=null then [FUNCTIONAL CLASS NAME] else [EB FUNCTIONAL CLASS NAME]),
TRANSFORM_FUNC_4 = Table.RemoveColumns(TRANSFORM_FUNC_3,{"EB FUNCTIONAL CLASS NAME", "FUNCTIONAL CLASS NAME"}),
TRANSFORM_FUNC_5 = Table.RenameColumns(TRANSFORM_FUNC_4,{{"FUNCTIONAL CLASS NAME MERGE", "FUNCTIONAL CLASS NAME"}})
in
TRANSFORM_FUNC_5I hope the issue will stand out.
