Forum Discussion

Helper IV

2 years ago

Solved

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

Hi Where do I start? I have NEVER used Power Query before. It looks way too complicated. I have a CSV file which when imported into Excel gives me the opportunity to transform data. Basi...

Need Help

Tutorial Requests

dufoq3

2 years ago

Is this what are you looking for? (I'm not sure about BARCODE).

You can find these columns at the end

Query refers to your CSV:

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:", "")),
    StepBack1 = #"Promoted Headers",
    // This step changes column name i.e. "Attribute:FEATURE" to "FEATURE" etc.
    RenamedAttributeColumns = Table.RenameColumns(StepBack1, 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})),
    AddedIndex = Table.AddIndexColumn(TransformedAttributeColumns, "Index", 0, 1, Int64.Type),
    MergedAttributeColumns = [v_attributeColumns = Table.SelectColumns(AddedIndex, {"Index"} & AttributeColumnNames[New]),
 v_changedTypePairs = Table.ToRows(Table.AddColumn(Table.FromList(Table.ColumnNames(v_attributeColumns)), "Type", each type text)),
 v_changedTypes = Table.TransformColumnTypes(v_attributeColumns, v_changedTypePairs),
 v_tableToListCombined = Table.ToList(v_changedTypes, each Text.Combine(_, ";")),
 v_tableFromList = Table.FromList(v_tableToListCombined, Splitter.SplitByNothing()),
 v_splitColumns = Table.SplitColumn(v_tableFromList, "Column1", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Index", "Attribute"}),
 v_changedTypeIndex = Table.TransformColumnTypes(v_splitColumns, {{"Index", Int64.Type}})
][v_changedTypeIndex],
    StepBack2 = AddedIndex,
    RemovedAttributeColumns = Table.RemoveColumns(StepBack2, AttributeColumnNames[New]),
    MergedQueryItself = Table.NestedJoin(RemovedAttributeColumns, {"Index"}, MergedAttributeColumns, {"Index"}, "MergedAttributeColumns", JoinKind.LeftOuter),
    #"Expanded MergedAttributeColumns" = Table.ExpandTableColumn(MergedQueryItself, "MergedAttributeColumns", {"Attribute"}, {"Attribute"}),
    // Rename from "ID" to "SKU" commented because of duplicity
    #"Renamed Columns" = Table.RenameColumns(#"Expanded MergedAttributeColumns",{/*{"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}}),
    Ad_Barcode = Table.AddColumn(TrimmedStartAndEndCategories, "Barcode", each Text.BetweenDelimiters([Attribute], "GTIN|", ";"), type text)
in
    Ad_Barcode

Rhothgar

2 years ago

dufoq3

This is as far as I managed to get:-

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),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged",  each Text.Trim(Text.TrimStart(Text.Trim(_), ";"), "_"), type text}})
in
    #"Trimmed Text"

You will see in the penultimate step I had lots of semi-colon delimiters. By playing around a bit I managed to get rid of all the ones at the start only.

I also tried a code with Text. TrimEnd but to no avail.

One very vaulable lesson I learned today was ALWAYS duplicate a query so if you make a mess then you always have your original backup. Basic stuff really!!!

Please refer to my previous post after your last one. Thanks again for all your help.

dufoq3

Community Champion

2 years ago

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"

Rhothgar

Helper IV

2 years ago

dufoq3

This is as far as I managed to get:-

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),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged",  each Text.Trim(Text.TrimStart(Text.Trim(_), ";"), "_"), type text}})
in
    #"Trimmed Text"

Forum Discussion

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

Related content

Table Transformation

Transform columns / type

Data transform using power query

Population dynamic graaph

Table Transformation