Transforming Data within adjacent Column Headers into one cell and populating that cell...

I realized that this had to be adjusted a bit. Now it should work (I still refer to XLSX example file!) 🙂

let
    Source = Excel.Workbook(File.Contents("y:\Downloads\PowerQuery\Rhothgar\EDITED ProductAll Home - Visible.xlsx"), true, true),
    in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
    AttributeColumnNames = Table.AddColumn(Table.FromList(List.Select(Table.ColumnNames(in_Sheet), each Text.StartsWith(_, "Attribute:")), null, {"Old"}), "New", each Text.Replace([Old], "Attribute:", "")),
    StepBackToSource = in_Sheet,
    // This step changes column name i.e. "Attribute:FEATURE" to "FEATURE" etc.
    RenamedAttributeColumns = Table.RenameColumns(StepBackToSource, Table.ToRows(AttributeColumnNames)),
    TransformedAttributeColumns = Table.TransformColumns(RenamedAttributeColumns, List.Transform(AttributeColumnNames[New], (colName)=> {colName, each if Text.BeforeDelimiter(_, ":") = "" then null else colName & "|" & Text.BeforeDelimiter(_, ":"), type text})),
    // Rename from "ID" to "SKU" commented because of duplicity
    #"Renamed Columns" = Table.RenameColumns(TransformedAttributeColumns,{/*{"ID", "SKU"},*/ {"CategoryPath", "Categories"}, {"Name", "Product Title"}, {"Code", "Part Number"}, {"Description", "Page content"}, {"ProductSummary", "Short description"}, {"Hidden", "Active"}, {"MetaDescription", "Meta description"}, {"MetaKeywords", "Meta keywords"}, {"Stock", "Quantity in stock"}, {"TaxRateID", "VAT rate"}, {"OrderLocation", "Warehouse Location"}, {"RelatedProducts", "Related Products"}, {"WebAddress", "URL Text"}, {"Image1Address", "Large Image"}, {"PromoStickers", "Product Label"}, {"CostPrice", "Cost Price"}, {"RRP", "List Price"}, {"Price", "Selling Price"}, {"OptionName", "Options"}, {"VariantNames", "Variants"}}),
    TransformedCategories = Table.TransformColumns(#"Renamed Columns",
{{"Categories", each Text.Trim(Text.AfterDelimiter(_, ">"))},
 {"CategoryManagement", each Text.Trim(Text.Replace(Text.Replace(Text.AfterDelimiter(_, ">"), ":", ", "), "Home > ", ""))}}),
    MergedCategories = Table.CombineColumns(TransformedCategories,{"Categories", "CategoryManagement"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Categories"),
    TrimmedStartAndEndCategories = Table.TransformColumns(MergedCategories,{{"Categories", each Text.Trim(Text.TrimStart(Text.TrimEnd(Text.Trim(_), ";"), ",")), type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(TrimmedStartAndEndCategories, "GTIN", "Barcode"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Barcode", each if _ = null then null else Text.AfterDelimiter(_, "|"), type text}})
in
    #"Extracted Text After Delimiter"

Note: with your edited step TransformedCategories resut is this:

So edit step TrimmedStartAndEndCategories like this: change quoted comma to quoted semicolon:

= Table.TransformColumns(MergedCategories,{{"Categories", each Text.Trim(Text.TrimStart(Text.TrimEnd(Text.Trim(_), ";"), ";")), type text}})

 To achieve this result:

BTW: Check also last step whre I extracted everything after "|" as a Barcode

If you want to refer to your CSV dataset, your code should be something like this:
I've deleted #"Chaged Type" step because it is really mess and you can do it anytime by simple select all data CTRL+A and then click Transform Tab --> Detect Data Type

let
    Source = Csv.Document(File.Contents("C:\Users\roger\Downloads\ProductAll Home - Visible.csv"),[Delimiter=",", Columns=296, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    AttributeColumnNames = Table.AddColumn(Table.FromList(List.Select(Table.ColumnNames(#"Promoted Headers"), each Text.StartsWith(_, "Attribute:")), null, {"Old"}), "New", each Text.Replace([Old], "Attribute:", "")),
    StepBack = #"Promoted Headers",
    // This step changes column name i.e. "Attribute:FEATURE" to "FEATURE" etc.
    RenamedAttributeColumns = Table.RenameColumns(StepBack, Table.ToRows(AttributeColumnNames)),
    TransformedAttributeColumns = Table.TransformColumns(RenamedAttributeColumns, List.Transform(AttributeColumnNames[New], (colName)=> {colName, each if Text.BeforeDelimiter(_, ":") = "" then null else colName & "|" & Text.BeforeDelimiter(_, ":"), type text})),
    // Rename from "ID" to "SKU" commented because of duplicity
    #"Renamed Columns" = Table.RenameColumns(TransformedAttributeColumns,{/*{"ID", "SKU"},*/ {"CategoryPath", "Categories"}, {"Name", "Product Title"}, {"Code", "Part Number"}, {"Description", "Page content"}, {"ProductSummary", "Short description"}, {"Hidden", "Active"}, {"MetaDescription", "Meta description"}, {"MetaKeywords", "Meta keywords"}, {"Stock", "Quantity in stock"}, {"TaxRateID", "VAT rate"}, {"OrderLocation", "Warehouse Location"}, {"RelatedProducts", "Related Products"}, {"WebAddress", "URL Text"}, {"Image1Address", "Large Image"}, {"PromoStickers", "Product Label"}, {"CostPrice", "Cost Price"}, {"RRP", "List Price"}, {"Price", "Selling Price"}, {"OptionName", "Options"}, {"VariantNames", "Variants"}}),
    TransformedCategories = Table.TransformColumns(#"Renamed Columns",
{{"Categories", each Text.Trim(Text.AfterDelimiter(_, ">"))},
 {"CategoryManagement", each Text.Trim(Text.Replace(Text.Replace(Text.AfterDelimiter(_, ">"), ":", ", "), "Home > ", ""))}}),
    MergedCategories = Table.CombineColumns(TransformedCategories,{"Categories", "CategoryManagement"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Categories"),
    TrimmedStartAndEndCategories = Table.TransformColumns(MergedCategories,{{"Categories", each Text.Trim(Text.TrimStart(Text.TrimEnd(Text.Trim(_), ";"), ",")), type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(TrimmedStartAndEndCategories, "GTIN", "Barcode"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Barcode", each if _ = null then null else Text.AfterDelimiter(_, "|"), type text}})
in
    #"Extracted Text After Delimiter"

Absolutely fantastic. Thank you so much.

I have since found out in a telephone conversation that headers do not need to be renamed but this is not an issue as the import system ignores them anyway. They are manually mapped it turns out.  At least, it was a valuable lesson for me.

I have deleted some columns from test file and that works perfectly so I am confident where that is concerned.

All that now remains is for me to look at your original code for bringing all 104 attribute column together and separating all valid cells (those without a null value) with a semi-colon.

So far, I have this result:-

let
    Source = Excel.Workbook(File.Contents("C:\Users\roger\OneDrive\Documents\FABRIC COMMUNITY\EDITED ProductAll Home - Visible.xlsx"), true, true),
    in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
    AttributeColumnNames = Table.AddColumn(Table.FromList(List.Select(Table.ColumnNames(in_Sheet), each Text.StartsWith(_, "Attribute:")), null, {"Old"}), "New", each Text.Replace([Old], "Attribute:", "")),
    StepBackToSource = in_Sheet,
    // This step changes column name i.e. "Attribute:FEATURE" to "FEATURE" etc.
    RenamedAttributeColumns = Table.RenameColumns(StepBackToSource, Table.ToRows(AttributeColumnNames)),
    TransformedAttributeColumns = Table.TransformColumns(RenamedAttributeColumns, List.Transform(AttributeColumnNames[New], (colName)=> {colName, each if Text.BeforeDelimiter(_, ":") = "" then null else colName & "|" & Text.BeforeDelimiter(_, ":"), type text})),
    // Rename from "ID" to "SKU" commented because of duplicity
    #"Renamed Columns" = Table.RenameColumns(TransformedAttributeColumns,{/*{"ID", "SKU"},*/ {"CategoryPath", "Categories"}, {"Name", "Product Title"}, {"Code", "Part Number"}, {"Description", "Page content"}, {"ProductSummary", "Short description"}, {"Hidden", "Active"}, {"Stock", "Quantity in stock"}, {"TaxRateID", "VAT rate"}, {"OrderLocation", "Warehouse Location"}, {"RelatedProducts", "Related Products"}, {"WebAddress", "URL Text"}, {"Image1Address", "Large Image"}, {"PromoStickers", "Product Label"}, {"CostPrice", "Cost Price"}, {"RRP", "List Price"}, {"Price", "Selling Price"}, {"OptionName", "Options"}, {"VariantNames", "Variants"}}),
    TransformedCategories = Table.TransformColumns(#"Renamed Columns",
{{"Categories", each Text.Trim(Text.AfterDelimiter(_, ">"))},
 {"CategoryManagement", each Text.Trim(Text.Replace(Text.Replace(Text.AfterDelimiter(_, ">"), ":", ", "), "Home > ", ""))}}),
    MergedCategories = Table.CombineColumns(TransformedCategories,{"Categories", "CategoryManagement"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Categories"),
    TrimmedStartAndEndCategories = Table.TransformColumns(MergedCategories,{{"Categories", each Text.Trim(Text.TrimStart(Text.TrimEnd(Text.Trim(_), ";"), ",")), type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(TrimmedStartAndEndCategories, "GTIN", "Barcode"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Barcode", each if _ = null then null else Text.AfterDelimiter(_, "|"), type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Text After Delimiter",{"ID", "Barcode", "Product Title", "Part Number", "Page content", "Short description", "Brand", "Selling Price", "List Price", "Large Image", "Image2Address", "Image3Address", "Image4Address", "Image5Address", "Image6Address", "Image7Address", "Image8Address", "Image9Address", "Image10Address", "Image11Address", "Image12Address", "Quantity in stock", "Weight", "VAT rate", "Condition", "SpecialOffer", "Warehouse Location", "OrderNote", "Active", "Categories", "CategoryManagementOrder", "Related Products", "Options", "OptionSize", "OptionType", "OptionValidation", "OptionItemName", "OptionItemPriceExtra", "OptionItemOrder", "OptionVariantOrder", "Variants", "VariantTypes", "VariantCategoryPage", "VariantChoiceName", "VariantItem1", "VariantItem1Data", "VariantItem2", "VariantItem2Data", "VariantItem3", "VariantItem3Data", "VariantItem4", "VariantItem4Data", "VariantItem5", "VariantItem5Data", "VariantDefault", "URL Text", "CanBeAddedToCart", "Product Label", "Cost Price", "TaxRateName", "OptionPlaceHolder", "FromQuantity", "BulkDiscountId", "AC", "ADWORDSCUSTOMLABEL0", "AGE", "AGEGROUP", "BEANTOCUP", "BIKESKEWER", "BLUETOOTH", "CADENCESENSOR", "CAGE", "CAPACITY", "CARRIER", "CATEGORY", "CERTIFICATE", "CODE", "COLOUR", "COMPATIBILITY", "CONNECTIVITY", "DESIGN", "DEVICES", "DIAMETER", "DOWNLOAD", "EAN", "EDITION", "EXCLUDEDDESTINATION", "EXCLUDEPRODUCTFEED", "FEATURE", "FINISH", "FIT", "GENDER", "GENERATION", "GENRE", "GTIN", "HANDLEBARTYPE", "HSCODE", "HSTARIFFCODE", "IDENTIFIER_EXISTS", "IDENTIFIEREXISTS", "ISBNNUMBER", "KEYBOARD", "LABEL", "LANGUAGE", "LENGTH", "LINKS", "MAKE", "Material", "MEDIA", "MILKFROTHERINCLUDED", "MODEL", "MODELNUMBER", "MOUNT", "MPN", "MULTIPACK", "NETWORKSTATUS", "PARTTYPE", "PERMANENT", "PLATFORM", "PLAYBACK", "POSITION", "POWER", "POWERSOURCE", "QUANTITY", "REFILLABLE", "RELEASEDATE", "RESISTANCE", "RESISTANCETYPE", "SCREENSIZE", "SECURITYRATING", "SERIES", "SET", "SHADE", "SHAPE", "SHIPPING_LABEL", "SIZE", "SKU", "SPEED", "SPORT", "STANDALONE", "STEERERTUBEDIAMETER", "STUDIO", "STYLE", "SUPPORT", "TEETH", "TRANSIT_TIME_LABEL", "TURBOTRAINER", "Type", "UPC", "USAGE", "VERSION", "VOLTAGE", "VOLUME", "WIDTH", "WIRED"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"AC", "ADWORDSCUSTOMLABEL0", "AGE", "AGEGROUP", "BEANTOCUP", "BIKESKEWER", "BLUETOOTH", "CADENCESENSOR", "CAGE", "CAPACITY", "CARRIER", "CATEGORY", "CERTIFICATE", "CODE", "COLOUR", "COMPATIBILITY", "CONNECTIVITY", "DESIGN", "DEVICES", "DIAMETER", "DOWNLOAD", "EAN", "EDITION", "EXCLUDEDDESTINATION", "EXCLUDEPRODUCTFEED", "FEATURE", "FINISH", "FIT", "GENDER", "GENERATION", "GENRE", "GTIN", "HANDLEBARTYPE", "HSCODE", "HSTARIFFCODE", "IDENTIFIER_EXISTS", "IDENTIFIEREXISTS", "ISBNNUMBER", "KEYBOARD", "LABEL", "LANGUAGE", "LENGTH", "LINKS", "MAKE", "Material", "MEDIA", "MILKFROTHERINCLUDED", "MODEL", "MODELNUMBER", "MOUNT", "MPN", "MULTIPACK", "NETWORKSTATUS", "PARTTYPE", "PERMANENT", "PLATFORM", "PLAYBACK", "POSITION", "POWER", "POWERSOURCE", "QUANTITY", "REFILLABLE", "RELEASEDATE", "RESISTANCE", "RESISTANCETYPE", "SCREENSIZE", "SECURITYRATING", "SERIES", "SET", "SHADE", "SHAPE", "SHIPPING_LABEL", "SIZE", "SKU", "SPEED", "SPORT", "STANDALONE", "STEERERTUBEDIAMETER", "STUDIO", "STYLE", "SUPPORT", "TEETH", "TRANSIT_TIME_LABEL", "TURBOTRAINER", "Type", "UPC", "USAGE", "VERSION", "VOLTAGE", "VOLUME", "WIDTH", "WIRED"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Attribute")
in
    #"Merged Columns"

Here is the screenshot of the result from your code I would like to use:-



I have been into that code but I think I have learned that you cannot simply copy and paste code between differents projects unless ALL variables are identical.

I've had a quick play around with trying to insert some of that code but not achieved correct result.  

I am now investigating whether it is identical code to the Transformed, Merged and Trimmed code.

The next big hurdle will be when this code is copied into the full spreadsheet.  There will be too much data I believe.

Our posts crossed!!!

I really don't know how you do it and SO FAST too!

Simply amazing!  LEGEND!

How long have you been working with PQ?

Have a great weekend and thanks again.

I will review completely next week and see if there is anything else but I am not sure there is.