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.

Rhothgar

Helper IV

2 years ago

OK. Thanks for the heads up.

Hopefully, I have done as instructed here.

Here I have created a blank workbook as per previous post but reduced it to only 5 lines to give a data example.

let
    Source = Excel.Workbook(File.Contents("C:\Users\roger\Downloads\ProductAll (7).xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Action", type text}, {"ID", Int64.Type}, {"CategoryPath", type text}, {"Name", type text}, {"Code", type text}, {"ProductSummary", type text}, {"Brand", type text}, {"Price", type number}, {"RRP", type number}, {"Image1", type text}, {"Image2", type text}, {"Image3", type text}, {"Image4", type text}, {"Image5", type text}, {"Image6", type any}, {"Image7", type any}, {"Image8", type any}, {"Image9", type any}, {"Image10", type any}, {"Image11", type any}, {"Image12", type any}, {"Image1Address", type text}, {"Image2Address", type text}, {"Image3Address", type text}, {"Image4Address", type text}, {"Image5Address", type text}, {"Image6Address", type any}, {"Image7Address", type any}, {"Image8Address", type any}, {"Image9Address", type any}, {"Image10Address", type any}, {"Image11Address", type any}, {"Image12Address", type any}, {"MetaTitle", type text}, {"MetaDescription", type text}, {"MetaKeywords", type text}, {"Stock", Int64.Type}, {"Weight", type number}, {"TaxRateID", Int64.Type}, {"Condition", type text}, {"SpecialOffer", type text}, {"OrderLocation", Int64.Type}, {"OrderNote", type any}, {"Hidden", type text}, {"CategoryManagement", type text}, {"CategoryManagementOrder", type text}, {"RelatedProducts", type text}, {"OptionName", type any}, {"OptionSize", type any}, {"OptionType", type any}, {"OptionValidation", type any}, {"OptionItemName", type any}, {"OptionItemPriceExtra", type any}, {"OptionItemOrder", type any}, {"OptionVariantOrder", type time}, {"VariantNames", type text}, {"VariantTypes", type text}, {"VariantCategoryPage", type text}, {"VariantChoiceName", type text}, {"VariantItem1", type text}, {"VariantItem1Data", type any}, {"VariantItem2", type text}, {"VariantItem2Data", type any}, {"VariantItem3", type text}, {"VariantItem3Data", type any}, {"VariantItem4", type any}, {"VariantItem4Data", type any}, {"VariantItem5", type any}, {"VariantItem5Data", type any}, {"VariantDefault", type text}, {"WebAddress", type text}, {"CanBeAddedToCart", type logical}, {"PromoStickers", type text}, {"CostPrice", Int64.Type}, {"TaxRateName", type text}, {"OptionPlaceHolder", type any}, {"BulkDiscountId", Int64.Type}, {"FromQuantity", Int64.Type}, {"Attribute:AC", type text}, {"Attribute:ADDEDEXTRAS", type any}, {"Attribute:ADWORDSCUSTOMLABEL0", type text}, {"Attribute:AGE", type text}, {"Attribute:AGEGROUP", type text}, {"Attribute:AVAILABILITY", type any}, {"Attribute:AVAILABILITYDATE", type any}, {"Attribute:BEANTOCUP", type any}, {"Attribute:BIKESKEWER", type any}, {"Attribute:BLUETOOTH", type any}, {"Attribute:CADENCESENSOR", type any}, {"Attribute:CAGE", type text}, {"Attribute:CAPACITY", type text}, {"Attribute:CARRIER", type text}, {"Attribute:CATEGORY", type text}, {"Attribute:CERTIFICATE", type text}, {"Attribute:CODE", type any}, {"Attribute:COFFEEPODS", type any}, {"Attribute:COLOUR", type text}, {"Attribute:COMPATIBILITY", type any}, {"Attribute:CONNECTIVITY", type any}, {"Attribute:DESIGN", type any}, {"Attribute:DEVELOPER", type text}, {"Attribute:DEVICES", type text}, {"Attribute:DIAMETER", type any}, {"Attribute:DOWNLOAD", type text}, {"Attribute:EAN", type text}, {"Attribute:EDITION", type text}, {"Attribute:EXCLUDEDDESTINATION", type text}, {"Attribute:EXCLUDEPRODUCTFEED", type text}, {"Attribute:EXPIRATIONDATE", type any}, {"Attribute:FEATURE", type text}, {"Attribute:FINISH", type any}, {"Attribute:FIT", type any}, {"Attribute:FLAVOUR", type any}, {"Attribute:GENDER", type text}, {"Attribute:GENERATION", type text}, {"Attribute:GENRE", type text}, {"Attribute:GTIN", type text}, {"Attribute:HANDLEBARTYPE", type any}, {"Attribute:HSCODE", type any}, {"Attribute:HSTARIFFCODE", type any}, {"Attribute:IDENTIFIER_EXISTS", type text}, {"Attribute:IDENTIFIEREXISTS", type text}, {"Attribute:ISBNNUMBER", type any}, {"Attribute:KEYBOARD", type any}, {"Attribute:LABEL", type any}, {"Attribute:LANGUAGE", type text}, {"Attribute:LENGTH", type text}, {"Attribute:MAKE", type text}, {"Attribute:MAPS", type any}, {"Attribute:Material", type text}, {"Attribute:MEDIA", type text}, {"Attribute:MILKFROTHERINCLUDED", type any}, {"Attribute:Model", type text}, {"Attribute:MODELNUMBER", type text}, {"Attribute:MOUNT", type any}, {"Attribute:MPN", type text}, {"Attribute:Multipack", type text}, {"Attribute:NETWORKSTATUS", type any}, {"Attribute:NOISELEVEL", type text}, {"Attribute:PARTTYPE", type any}, {"Attribute:Pattern", type text}, {"Attribute:PERMANENT", type any}, {"Attribute:PLATFORM", type text}, {"Attribute:PLAYBACK", type text}, {"Attribute:PLUSKIT", type any}, {"Attribute:PODSINCLUDED", type any}, {"Attribute:PORTABLE", type any}, {"Attribute:POSITION", type text}, {"Attribute:POWER", type any}, {"Attribute:POWERSOURCE", type any}, {"Attribute:QUANTITY", type text}, {"Attribute:REFILLABLE", type any}, {"Attribute:RELEASEDATE", type text}, {"Attribute:RESISTANCE", type any}, {"Attribute:RESISTANCETYPE", type any}, {"Attribute:SCREENSIZE", type any}, {"Attribute:SECURITYRATING", type any}, {"Attribute:SERIES", type any}, {"Attribute:SET", type any}, {"Attribute:SHADE", type text}, {"Attribute:SHAPE", type text}, {"Attribute:SHIPPING_LABEL", type any}, {"Attribute:Size", type text}, {"Attribute:SKU", type text}, {"Attribute:SPEED", type text}, {"Attribute:SPORT", type any}, {"Attribute:STANDALONE", type any}, {"Attribute:STEERERTUBEDIAMETER", type any}, {"Attribute:STUDIO", type text}, {"Attribute:STYLE", type any}, {"Attribute:SUPPORT", type any}, {"Attribute:TEETH", type text}, {"Attribute:TURBOTRAINER", type any}, {"Attribute:Type", type text}, {"Attribute:UPC", type text}, {"Attribute:USAGE", type text}, {"Attribute:VALUE", type any}, {"Attribute:VERSION", type text}, {"Attribute:VOLTAGE", type text}, {"Attribute:VOLUME", type text}, {"Attribute:WIDTH", type text}, {"Attribute:WIRED", type any}, {"Delivery:1000g Bulgaria DDP", type text}, {"Delivery:1000g DDP Austria", type text}, {"Delivery:2000g Bulgaria DDP", type text}, {"Delivery:2000g DDP Austria", type text}, {"Delivery:250g Bulgaria DDP", type text}, {"Delivery:250g Croatia DDP", type text}, {"Delivery:250g DDP Austria", type text}, {"Delivery:2nd Chargeable", type text}, {"Delivery:2nd Free", type text}, {"Delivery:500g Bulgaria DDP", type text}, {"Delivery:500g Croatia DDP", type text}, {"Delivery:500g DDP Austria", type text}, {"Delivery:500g DDP Belgium", type text}, {"Delivery:500g DDP Luxembourg", type text}, {"Delivery:500g DDP Malta", type text}, {"Delivery:500g DDP Romania", type text}, {"Delivery:500g Denmark DDP", type text}, {"Delivery:500g France DDP", type text}, {"Delivery:500g Germany DDP", type text}, {"Delivery:500g Malta", type text}, {"Delivery:750g Bulgaria DDP", type text}, {"Delivery:750g DDP Austria", type text}, {"Delivery:Austria Evri Non-DDP", type text}, {"Delivery:Belgium Evri Non-DDP", type text}, {"Delivery:Bulgaria Evri Non-DDP", type text}, {"Delivery:Collect From Store", type text}, {"Delivery:Courier (Highlands and Islands)", type text}, {"Delivery:Courier Islands and Offshore (2 -5 day)", type text}, {"Delivery:Courier Scottish Highlands (2 day delivery)", type text}, {"Delivery:Courier Shipping with Insurance", type text}, {"Delivery:Croatia Evri Non-DDP", type text}, {"Delivery:Cyprus Evri Non-DDP", type text}, {"Delivery:Czech Republic Evri Non-DDP", type text}, {"Delivery:Denmark Evri Non-DDP", type text}, {"Delivery:Estonia Evri Non-DDP", type text}, {"Delivery:Europe", type text}, {"Delivery:Europe 1kg", type text}, {"Delivery:Evri (Duty Delivery Paid) - Delivery Aim 3 - 7 working days", type text}, {"Delivery:Evri Parcelshop Tracked Free below £20 (Highlands and Islands)", type text}, {"Delivery:Evri Tracked Free £9.99-£29.99 value (includes disallowed postcodes which are not turned on currently)", type text}, {"Delivery:Evri Tracked Free Below £9.99 value (includes disallowed postcodes which are not turned on currently)(2)", type text}, {"Delivery:Evri Tracked upto 1kg", type text}, {"Delivery:Finland Evri Non-DDP", type text}, {"Delivery:France Evri Non-DDP", type text}, {"Delivery:Free (Hermes 3 - 5 Working Days)", type text}, {"Delivery:FREE Delivery over £100.00", type text}, {"Delivery:Germany Evri Non-DDP", type text}, {"Delivery:Greece Evri Non-DDP", type text}, {"Delivery:Half Pallet Economy - Zone 10", type text}, {"Delivery:Half Pallet Economy - Zone 11", type text}, {"Delivery:Half Pallet Economy - Zone 12", type text}, {"Delivery:Half Pallet Economy - Zone 13", type text}, {"Delivery:Half Pallet Economy - Zone 14", type text}, {"Delivery:Half Pallet Economy - Zone 15", type text}, {"Delivery:Half Pallet Economy - Zone 16", type text}, {"Delivery:Half Pallet Economy - Zone 17", type text}, {"Delivery:Half Pallet Economy - Zone 18", type text}, {"Delivery:Half Pallet Economy - Zone 22", type text}, {"Delivery:Half Pallet Economy - Zone 4", type text}, {"Delivery:Half Pallet Economy - Zone 6", type text}, {"Delivery:Half Pallet Economy - Zone 7", type text}, {"Delivery:Half Pallet Economy - Zone 8", type text}, {"Delivery:Half Pallet Economy - Zone 9", type text}, {"Delivery:Half Pallet Economy - Zones 1, 2, 3 and 5", type text}, {"Delivery:Half Pallet Next Day - Zone 13", type text}, {"Delivery:Half Pallet Next Day - Zones 1-3", type text}, {"Delivery:Half Pallet Next Day - Zones 4-5", type text}, {"Delivery:Half Pallet Next Day - Zones 6-8, 11", type text}, {"Delivery:Hungary (Duty Delivery Paid) - Delivery Aim 12 - 14 days", type text}, {"Delivery:Hungary Evri Non-DDP", type text}, {"Delivery:Ireland Evri Non-DDP", type text}, {"Delivery:Italy Evri Non-DDP", type text}, {"Delivery:Latvia Evri Non-DDP", type text}, {"Delivery:Lithuania Evri Non-DDP", type text}, {"Delivery:Luxembourg Evri Non-DDP", type text}, {"Delivery:Malta Evri Non-DDP", type text}, {"Delivery:Netherlands Evri Non-DDP", type text}, {"Delivery:Over 2 kg. Courier (UK  Mainland)", type text}, {"Delivery:Pallets Zones 10, 14, 16-17 Next Day 1/2 (CA, DG, EH, FK, G1-G90, KA, KY, LA11-23, LD, LL35-78, ML, N1-8, N10, N15-19, N22, NW1-3, NW5-6, NW8, NW10-11, PA1-19, PL, SA14-73, SA99, SE3-5, SE7-8, SE10, SE13-17, SE21-24, SY,  SW2, SW4, SW6, SW8-9, SW11, SW13)", type text}, {"Delivery:Pallets Zones 12 Next Day 1/2 (SW3, SW5, SW7, SW10, W2, W8, W10-11, W14)", type text}, {"Delivery:Pallets Zones 15 Next Day 1/2 (EC1-4, SE1, SE11, SW1, W1, WC)", type text}, {"Delivery:Pallets Zones 18 Next Day 1/2 (EC1-4, SE1, SE11, SW1, W1, WC)", type text}, {"Delivery:Pallets Zones 19 Next Day 1/2 (AB10-16, AB21-25, AB30, AB39)", type text}, {"Delivery:Pallets Zones 9 Next Day 1/2 (CT, SA1-SA13)", type text}, {"Delivery:ParcelForce 24", type text}, {"Delivery:ParcelForce 48", type text}, {"Delivery:Placeholder for Disallowed Highlands and Island Postcode", type text}, {"Delivery:Poland Evri Non-DDP", type text}, {"Delivery:Portugal Evri Non-DDP", type text}, {"Delivery:RM Large Letter - Europe Zones 1, 2 and 3 (International Tracked and Signed)", type text}, {"Delivery:RM Large Letter Tracked 24 (Cost + 20%)", type text}, {"Delivery:RM Large Letter Tracked 48 (Cost + 20%)", type text}, {"Delivery:RM Medium Parcel Tracked 24 (Cost+20%)", type text}, {"Delivery:RM Medium Parcel Tracked 48 (Cost+20%)", type text}, {"Delivery:RM Small Parcel - Europe Zone 2 (International Tracked and Signed)", type text}, {"Delivery:RM Small Parcel - Europe Zone 3 (International Tracked and Signed)", type text}, {"Delivery:RM Small Parcel - Europe Zones 1 (International Tracked and Signed)", type text}, {"Delivery:RM Small Parcel Tracked 24 (Cost+20%)", type text}, {"Delivery:RM Small Parcel Tracked 48 (Cost +20%)", type text}, {"Delivery:RM Small Parcel World Zone 3", type text}, {"Delivery:RM Special Delivery 1pm", type text}, {"Delivery:RM Special Delivery 9am", type text}, {"Delivery:Romania Evri Non-DDP", type text}, {"Delivery:Royal Mail - World Zone 2", type text}, {"Delivery:Royal Mail (Up to £50) Upgrade", type text}, {"Delivery:Royal Mail £29.99-£50", type text}, {"Delivery:Royal Mail 1st Class Signed For", type text}, {"Delivery:Royal Mail 1st Class Signed For (Weight Based)", type text}, {"Delivery:Royal Mail 2nd Class Signed For", type text}, {"Delivery:Royal Mail Tracked 24", type text}, {"Delivery:Royal Mail Tracked 48", type text}, {"Delivery:Royal Mail World Zone 1 - Large Letter (International Tracked and Signed)", type text}, {"Delivery:Royal Mail World Zone 1 - Small Parcel (International Tracked and Signed)", type text}, {"Delivery:Slovakia Evri Non-DDP", type text}, {"Delivery:Slovenia Evri Non-DDP", type text}, {"Delivery:Spain Evri Non-DDP", type text}, {"Delivery:Sweden Evri Non-DDP", type text}, {"Delivery:Tension Arm (UPS Upgrade)", type text}, {"Delivery:Tension Arm Free Postage", type text}, {"Delivery:UK Outlying Areas", type text}, {"Delivery:UPS Duty Delivery Paid", type text}, {"Delivery:UPS Express Delivery (Highlands and Remote Areas)", type text}, {"Delivery:UPS Next Day", type text}, {"Delivery:UPS Next Day Delivery (Zone 1 only)", type text}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",500),
    #"Removed Bottom Rows1" = Table.RemoveLastN(#"Removed Bottom Rows",13),
ReplacedValueAllColumnsDynamic = Table.ReplaceValue(Source,"Attribute:","",Replacer.ReplaceText, Table.ColumnNames(Source)),
#"Promoted Headers" = Table.PromoteHeaders(ReplacedValueAllColumnsDynamic, [PromoteAllScalars=true]),
TransformedColumns = Table.TransformColumns(
#"Promoted Headers",
List.Transform(Table.ColumnNames(#"Promoted Headers"),
(colName)=> {colName, each if Text.BeforeDelimiter(_, ":") = "" then null else colName & "|" & Text.BeforeDelimiter(_, ":"), type text}
)),
#"Merged Columns" = Table.FromList(Table.ToList(TransformedColumns, each Text.Combine(_, ";")))
in
#"Merged Columns"

As soon as I add your earlier code it falls over.

dufoq3

Community Champion

2 years ago

1 important note here: delete "Changed Type" step because it hardcodes column names. Your code will be much shorter. It is good behavior to change types at the end of query (or if it is necessary for next steps)

Rhothgar
Helper IV
2 years ago
Thank you. I've not had time to look at it today. Maybe tomorrow. Thanks again.
Rhothgar
Helper IV
2 years ago
Hi.

I've now turned my attention to another column in order to try and simplify my understanding.

I had this in a column of a worksheet with the overall spreadsheet:-

I then transformed it to this:-

Using this code:-

let Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content], #"Replaced Value" = Table.ReplaceValue(Source,">","|",Replacer.ReplaceText,{"CategoryPath"}) in #"Replaced Value"

GREAT!

So a couple of things. I notice that it refers to Table14 but I have renamed the worksheet "TransformCategoryPath".

So I was expecting to simply by able to go to cell AW37 in another worksheet within the same spreadsheet and =TransformCategoryPath!A2"

When I hit enter after selecting cell A2 in the relevant query "TransformCategoryPath" changes to "Home | Arts & Crafts" as expected.

However, when I then go to Data and refresh, the formula in AW37 vanished and "Home > Arts & Crafts" appears again.

What am I doing wrong please?

Do I need to index the query and link it in another way can you tell me?

The ID is unique so I should perhaps include that somehow?

Oh and when I created the query I simply clicked on the column header.

The query has a few rows...

NB I deleted the space out of the query name just in case that was causing an issue.
Rhothgar
Helper IV
2 years ago
Thank you.

If a "Changed Type" is deleted, can making edits before where the "Changed Type" was previously located then cause an error?

Rhothgar

Helper IV

2 years ago

dufoq3

I am now well on with this and I am hopefully going to finalise the file today but have come across another stumbling block.

I am a new file now with a link to a local file. I've changed a lot in terms of column names and reordering just to tidy up the data and have had a play trying to insert your ATTRIBUTE code into the relevant position in Advanced Editor. So I have copied the query from the worksheet where I'd got it working.

So obviously the source has changed in my new file.

SO here is my latest code:-

The errors occur in the columns which I have either moved or re-ordered laterly in the code.

The errors actually introduced when I click on the first instance of "Transformed Columns".

Columns ID, Price, RRP, Stock, Weight, TaxRateID, Special Offer (I think this may have merged with "Active" column somehow), Cost Price all seem to now have errors whereas they didn't before I attempted to add your code in.

Also spotted, the Categories has now become "CategoryPath" and merged with something else.

It's actually more of a mess than I thought it was!!!!

Hope you can decipher this post as I've been typing it on and off over a few hours and coming back to it....

let
    Source = Csv.Document(File.Contents("C:\Users\roger\Downloads\ProductAll Home - Visible.csv"),[Delimiter=",", Columns=296, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    ReplacedValueAllColumnsDynamic = Table.ReplaceValue(Source,"ATTRIBUTE:","",Replacer.ReplaceText, Table.ColumnNames(Source)),
    #"Promoted Headers" = Table.PromoteHeaders(ReplacedValueAllColumnsDynamic, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Action", type text}, {"ID", Int64.Type}, {"CategoryPath", type text}, {"Name", type text}, {"Code", type text}, {"Description", type text}, {"ProductSummary", type text}, {"Brand", type text}, {"Price", type number}, {"RRP", type number}, {"Image1", type text}, {"Image2", type text}, {"Image3", type text}, {"Image4", type text}, {"Image5", type text}, {"Image6", type text}, {"Image7", type text}, {"Image8", type text}, {"Image9", type text}, {"Image10", type text}, {"Image11", type text}, {"Image12", type text}, {"Image1Address", type text}, {"Image2Address", type text}, {"Image3Address", type text}, {"Image4Address", type text}, {"Image5Address", type text}, {"Image6Address", type text}, {"Image7Address", type text}, {"Image8Address", type text}, {"Image9Address", type text}, {"Image10Address", type text}, {"Image11Address", type text}, {"Image12Address", type text}, {"MetaTitle", type text}, {"MetaDescription", type text}, {"MetaKeywords", type text}, {"Stock", Int64.Type}, {"Weight", type number}, {"TaxRateID", Int64.Type}, {"Condition", type text}, {"SpecialOffer", type text}, {"OrderLocation", Int64.Type}, {"OrderNote", type text}, {"Hidden", type text}, {"CategoryManagement", type text}, {"CategoryManagementOrder", type text}, {"RelatedProducts", type text}, {"OptionName", type text}, {"OptionSize", type text}, {"OptionType", type text}, {"OptionValidation", type text}, {"OptionItemName", type text}, {"OptionItemPriceExtra", type text}, {"OptionItemOrder", type text}, {"OptionVariantOrder", type text}, {"VariantNames", type text}, {"VariantTypes", type text}, {"VariantCategoryPage", type text}, {"VariantChoiceName", type text}, {"VariantItem1", type text}, {"VariantItem1Data", type text}, {"VariantItem2", type text}, {"VariantItem2Data", type text}, {"VariantItem3", type text}, {"VariantItem3Data", type text}, {"VariantItem4", type text}, {"VariantItem4Data", type text}, {"VariantItem5", type text}, {"VariantItem5Data", type text}, {"VariantDefault", type text}, {"WebAddress", type text}, {"CanBeAddedToCart", type logical}, {"PromoStickers", type text}, {"CostPrice", type number}, {"TaxRateName", type text}, {"OptionPlaceHolder", type text}, {"FromQuantity", Int64.Type}, {"BulkDiscountId", Int64.Type}, {"Attribute:AC", type text}, {"Attribute:ADWORDSCUSTOMLABEL0", type text}, {"Attribute:AGE", type text}, {"Attribute:AGEGROUP", type text}, {"Attribute:BEANTOCUP", type text}, {"Attribute:BIKESKEWER", type text}, {"Attribute:BLUETOOTH", type text}, {"Attribute:CADENCESENSOR", type text}, {"Attribute:CAGE", type text}, {"Attribute:CAPACITY", type text}, {"Attribute:CARRIER", type text}, {"Attribute:CATEGORY", type text}, {"Attribute:CERTIFICATE", type text}, {"Attribute:CODE", type text}, {"Attribute:COLOUR", type text}, {"Attribute:COMPATIBILITY", type text}, {"Attribute:CONNECTIVITY", type text}, {"Attribute:DESIGN", type text}, {"Attribute:DEVICES", type text}, {"Attribute:DIAMETER", type text}, {"Attribute:DOWNLOAD", type text}, {"Attribute:EAN", type text}, {"Attribute:EDITION", type text}, {"Attribute:EXCLUDEDDESTINATION", type text}, {"Attribute:EXCLUDEPRODUCTFEED", type text}, {"Attribute:FEATURE", type text}, {"Attribute:FINISH", type text}, {"Attribute:FIT", type text}, {"Attribute:GENDER", type text}, {"Attribute:GENERATION", type text}, {"Attribute:GENRE", type text}, {"Attribute:GTIN", type text}, {"Attribute:HANDLEBARTYPE", type text}, {"Attribute:HSCODE", type text}, {"Attribute:HSTARIFFCODE", type text}, {"Attribute:IDENTIFIER_EXISTS", type text},{"Attribute:IDENTIFIEREXISTS", type text}, {"Attribute:ISBNNUMBER", type text}, {"Attribute:KEYBOARD", type text}, {"Attribute:LABEL", type text}, {"Attribute:LANGUAGE", type text}, {"Attribute:LENGTH", type text}, {"Attribute:LINKS", type text}, {"Attribute:MAKE", type text}, {"Attribute:Material", type text}, {"Attribute:MEDIA", type text}, {"Attribute:MILKFROTHERINCLUDED", type text}, {"Attribute:MODEL", type text}, {"Attribute:MODELNUMBER", type text}, {"Attribute:MOUNT", type text}, {"Attribute:MPN", type text}, {"Attribute:MULTIPACK", type text}, {"Attribute:NETWORKSTATUS", type text}, {"Attribute:PARTTYPE", type text}, {"Attribute:PERMANENT", type text}, {"Attribute:PLATFORM", type text}, {"Attribute:PLAYBACK", type text}, {"Attribute:POSITION", type text}, {"Attribute:POWER", type text}, {"Attribute:POWERSOURCE", type text}, {"Attribute:QUANTITY", type text}, {"Attribute:REFILLABLE", type text}, {"Attribute:RELEASEDATE", type text}, {"Attribute:RESISTANCE", type text}, {"Attribute:RESISTANCETYPE", type text}, {"Attribute:SCREENSIZE", type text}, {"Attribute:SECURITYRATING", type text}, {"Attribute:SERIES", type text}, {"Attribute:SET", type text}, {"Attribute:SHADE", type text}, {"Attribute:SHAPE", type text}, {"Attribute:SHIPPING_LABEL", type text}, {"Attribute:SIZE", type text}, {"Attribute:SKU", type text}, {"Attribute:SPEED", type text}, {"Attribute:SPORT", type text}, {"Attribute:STANDALONE", type text}, {"Attribute:STEERERTUBEDIAMETER", type text}, {"Attribute:STUDIO", type text}, {"Attribute:STYLE", type text}, {"Attribute:SUPPORT", type text}, {"Attribute:TEETH", type text}, {"Attribute:TRANSIT_TIME_LABEL", type text}, {"Attribute:TURBOTRAINER", type text}, {"Attribute:Type", type text}, {"Attribute:UPC", type text}, {"Attribute:USAGE", type text}, {"Attribute:VERSION", type text}, {"Attribute:VOLTAGE", type text}, {"Attribute:VOLUME", type text}, {"Attribute:WIDTH", type text}, {"Attribute:WIRED", type text}, {"Delivery:1000g Bulgaria DDP", type text}, {"Delivery:1000g DDP Austria", type text}, {"Delivery:2000g Bulgaria DDP", type text}, {"Delivery:2000g DDP Austria", type text}, {"Delivery:250g Bulgaria DDP", type text}, {"Delivery:250g Croatia DDP", type text}, {"Delivery:250g DDP Austria", type text}, {"Delivery:2nd Chargeable", type text}, {"Delivery:2nd Free", type text}, {"Delivery:500g Bulgaria DDP", type text}, {"Delivery:500g Croatia DDP", type text}, {"Delivery:500g DDP Austria", type text}, {"Delivery:500g DDP Belgium", type text}, {"Delivery:500g DDP Luxembourg", type text}, {"Delivery:500g DDP Malta", type text}, {"Delivery:500g DDP Romania", type text}, {"Delivery:500g Denmark DDP", type text}, {"Delivery:500g France DDP", type text}, {"Delivery:500g Germany DDP", type text}, {"Delivery:500g Malta", type text}, {"Delivery:750g Bulgaria DDP", type text}, {"Delivery:750g DDP Austria", type text}, {"Delivery:Austria Evri Non-DDP", type text}, {"Delivery:Belgium Evri Non-DDP", type text}, {"Delivery:Bulgaria Evri Non-DDP", type text}, {"Delivery:Collect From Store", type text}, {"Delivery:Courier (Highlands and Islands)", type text}, {"Delivery:Courier Islands and Offshore (2 -5 day)", type text}, {"Delivery:Courier Scottish Highlands (2 day delivery)", type text}, {"Delivery:Courier Shipping with Insurance", type text}, {"Delivery:Croatia Evri Non-DDP", type text}, {"Delivery:Cyprus Evri Non-DDP", type text}, {"Delivery:Czech Republic Evri Non-DDP", type text}, {"Delivery:Denmark Evri Non-DDP", type text}, {"Delivery:Estonia Evri Non-DDP", type text}, {"Delivery:Europe", type text}, {"Delivery:Europe 1kg", type text}, {"Delivery:Evri (Duty Delivery Paid) - Delivery Aim 3 - 7 working days", type text}, {"Delivery:Evri Parcelshop Tracked Free below £20 (Highlands and Islands)", type text}, {"Delivery:Evri Tracked Free £9.99-£29.99 value (includes disallowed postcodes which are not turned on currently)", type text}, {"Delivery:Evri Tracked Free Below £9.99 value (includes disallowed postcodes which are not turned on currently)(2)", type text}, {"Delivery:Evri Tracked upto 1kg", type text}, {"Delivery:Finland Evri Non-DDP", type text}, {"Delivery:France Evri Non-DDP", type text}, {"Delivery:Free (Evri 3 - 5 Working Days)", type text}, {"Delivery:FREE Delivery over £100.00", type text}, {"Delivery:Germany Evri Non-DDP", type text}, {"Delivery:Greece Evri Non-DDP", type text}, {"Delivery:Half Pallet Economy - Zone 10", type text}, {"Delivery:Half Pallet Economy - Zone 11", type text}, {"Delivery:Half Pallet Economy - Zone 12", type text}, {"Delivery:Half Pallet Economy - Zone 13", type text}, {"Delivery:Half Pallet Economy - Zone 14", type text}, {"Delivery:Half Pallet Economy - Zone 15", type text}, {"Delivery:Half Pallet Economy - Zone 16", type text}, {"Delivery:Half Pallet Economy - Zone 17", type text}, {"Delivery:Half Pallet Economy - Zone 18", type text}, {"Delivery:Half Pallet Economy - Zone 22", type text}, {"Delivery:Half Pallet Economy - Zone 4", type text}, {"Delivery:Half Pallet Economy - Zone 6", type text}, {"Delivery:Half Pallet Economy - Zone 7", type text}, {"Delivery:Half Pallet Economy - Zone 8", type text}, {"Delivery:Half Pallet Economy - Zone 9", type text}, {"Delivery:Half Pallet Economy - Zones 1, 2, 3 and 5", type text}, {"Delivery:Half Pallet Next Day - Zone 13", type text}, {"Delivery:Half Pallet Next Day - Zones 1-3", type text}, {"Delivery:Half Pallet Next Day - Zones 4-5", type text}, {"Delivery:Half Pallet Next Day - Zones 6-8, 11", type text}, {"Delivery:Hungary (Duty Delivery Paid) - Delivery Aim 12 - 14 days", type text}, {"Delivery:Hungary Evri Non-DDP", type text}, {"Delivery:Ireland Evri Non-DDP", type text}, {"Delivery:Italy Evri Non-DDP", type text}, {"Delivery:Latvia Evri Non-DDP", type text}, {"Delivery:Lithuania Evri Non-DDP", type text}, {"Delivery:Luxembourg Evri Non-DDP", type text}, {"Delivery:Malta Evri Non-DDP", type text}, {"Delivery:Netherlands Evri Non-DDP", type text}, {"Delivery:Over 2 kg. Courier (UK  Mainland)", type text}, {"Delivery:Pallets Zones 10, 14, 16-17 Next Day 1/2 (CA, DG, EH, FK, G1-G90, KA, KY, LA11-23, LD, LL35-78, ML, N1-8, N10, N15-19, N22, NW1-3, NW5-6, NW8, NW10-11, PA1-19, PL, SA14-73, SA99, SE3-5, SE7-8, SE10, SE13-17, SE21-24, SY,  SW2, SW4, SW6, SW8-9, SW11, SW13)", type text}, {"Delivery:Pallets Zones 12 Next Day 1/2 (SW3, SW5, SW7, SW10, W2, W8, W10-11, W14)", type text}, {"Delivery:Pallets Zones 15 Next Day 1/2 (EC1-4, SE1, SE11, SW1, W1, WC)", type text}, {"Delivery:Pallets Zones 18 Next Day 1/2 (EC1-4, SE1, SE11, SW1, W1, WC)", type text}, {"Delivery:Pallets Zones 19 Next Day 1/2 (AB10-16, AB21-25, AB30, AB39)", type text}, {"Delivery:Pallets Zones 9 Next Day 1/2 (CT, SA1-SA13)", type text}, {"Delivery:ParcelForce 24", type text}, {"Delivery:ParcelForce 24 Free", type text}, {"Delivery:ParcelForce 48", type text}, {"Delivery:Placeholder for Disallowed Highlands and Island Postcode", type text}, {"Delivery:Poland Evri Non-DDP", type text}, {"Delivery:Portugal Evri Non-DDP", type text}, {"Delivery:RM Large Letter - Europe Zones 1, 2 and 3 (International Tracked and Signed)", type text}, {"Delivery:RM Large Letter Tracked 24 (Cost + 20%)", type text}, {"Delivery:RM Large Letter Tracked 48 (Cost + 20%)", type text}, {"Delivery:RM Medium Parcel Tracked 24 (Cost+20%)", type text}, {"Delivery:RM Medium Parcel Tracked 48 (Cost+20%)", type text}, {"Delivery:RM Small Parcel - Europe Zone 2 (International Tracked and Signed)", type text}, {"Delivery:RM Small Parcel - Europe Zone 3 (International Tracked and Signed)", type text}, {"Delivery:RM Small Parcel - Europe Zones 1 (International Tracked and Signed)", type text}, {"Delivery:RM Small Parcel Tracked 24 (Cost+20%)", type text}, {"Delivery:RM Small Parcel Tracked 48 (Cost +20%)", type text}, {"Delivery:RM Small Parcel World Zone 3", type text}, {"Delivery:RM Special Delivery 1pm", type text}, {"Delivery:RM Special Delivery 9am", type text}, {"Delivery:Romania Evri Non-DDP", type text}, {"Delivery:Royal Mail - World Zone 2", type text}, {"Delivery:Royal Mail (Up to £50) Upgrade", type text}, {"Delivery:Royal Mail £29.99-£50", type text}, {"Delivery:Royal Mail 1st Class Signed For", type text}, {"Delivery:Royal Mail 1st Class Signed For (Weight Based)", type text}, {"Delivery:Royal Mail 2nd Class Signed For", type text}, {"Delivery:Royal Mail Tracked 24", type text}, {"Delivery:Royal Mail Tracked 48", type text}, {"Delivery:Royal Mail World Zone 1 - Large Letter (International Tracked and Signed)", type text}, {"Delivery:Royal Mail World Zone 1 - Small Parcel (International Tracked and Signed)", type text}, {"Delivery:Slovakia Evri Non-DDP", type text}, {"Delivery:Slovenia Evri Non-DDP", type text}, {"Delivery:Spain Evri Non-DDP", type text}, {"Delivery:Sweden Evri Non-DDP", type text}, {"Delivery:Tension Arm (UPS Upgrade)", type text}, {"Delivery:Tension Arm Free Postage", type text}, {"Delivery:UK Outlying Areas", type text}, {"Delivery:UPS Duty Delivery Paid", type text}, {"Delivery:UPS Express Delivery (Highlands and Remote Areas)", type text}, {"Delivery:UPS Next Day", type text}, {"Delivery:UPS Next Day Delivery (Zone 1 only)", type text}}), TransformedColumns = Table.TransformColumns(#"Changed Type", List.Transform(Table.ColumnNames(#"Changed Type"), (colName)=> {colName, each if Text.BeforeDelimiter(_, ":") = "" then null else colName & "|" & Text.BeforeDelimiter(_, ":"), type text})),
    #"Renamed Columns" = Table.RenameColumns(TransformedColumns,{{"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"}}),

    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Categories", "CategoryManagement"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Categories.1"),
    #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns",{{"Categories.1", "Categories"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",">","|",Replacer.ReplaceText,{"Categories"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",":",";",Replacer.ReplaceText,{"Related Products"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Categories", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Categories.1", "Categories.2", "Categories.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Categories.1", type text}, {"Categories.2", type text}, {"Categories.3", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Categories.1", Splitter.SplitTextByEachDelimiter({" | "}, QuoteStyle.Csv, false), {"Categories.1.1", "Categories.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Categories.1.1", type text}, {"Categories.1.2", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type2","Home | ","",Replacer.ReplaceText,{"Categories.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value2", "Categories.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Categories.2.1", "Categories.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Categories.2.1", type text}, {"Categories.2.2", type text}}),
    #"Replaced Value3" = Table.ReplaceValue(#"Changed Type3",null,"Home",Replacer.ReplaceValue,{"Categories.1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value3",{"Categories.1.1", "Categories.3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"Categories.1.2", "Categories.2.1", "Categories.2.2"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Categories"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns1",{{"Categories", each Text.TrimEnd(_, ";"), type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Trimmed Text", "Attribute:GTIN", "Attribute:GTIN - Copy"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Attribute:GTIN - Copy", each Text.BeforeDelimiter(_, ":"), type text}}),
    #"RenamedColumns 2" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute:GTIN - Copy", "Bar code"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"RenamedColumns 2",{{"SKU", Int64.Type}})
in
    #"Changed Type4"

This is as far as I have got without syntax errors but it says it cannot find Attribute:AC. Is this something to do with dynamic values but my source being static and set up differently as I didn't have your input for the new file?

dufoq3
Community Champion
2 years ago
Hi, I'm affraid that the only way how I can help you is exactly with your original csv. Share it with me and then I can see what we can do. At least 25 rows of that csv. If there is some sensitive data - just chage it.
- Rhothgar
  Helper IV
  2 years ago
  Thanks. That's cool.
  
  Have been dragged away for a couple of hours. I will still try and get it back to where I was earlier if I can recall where I stitched your code in.
  
  Ideally, I'd like to use the XML I can get but I changing it would be challenging. I'd probably need to use find/replace.
  CSV would be quicker option but not final solution.

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