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

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.

dufoq3

Community Champion

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
Helper IV
2 years ago
Our posts crossed!!!
Rhothgar
Helper IV
2 years ago
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.

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