Forum Discussion
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...
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_Barcodedufoq3
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.
Here is some code which is basically the source file linked to a blank workbook.
I'm not sure that this is what you are looking for.
So - you have to learn few importatnt things:
- always reply to correct post! (because last 2 you did not)
- If someone ask you for your code or at least example - you should create some data example via button ENTER DATA (you can see my prev. post with linked video) and then you shoud copy all your code (but CTRL+C in Advanced Editor) and paste here (not as screenshot but as text). It is good to use this button for pasting the code:
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.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)