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.
sorry, I missed this post. You have to just add space after ; in last step Merged Columns
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7Vlbc5s4FP4rTJ67u1x9e5NB2BoDopKIk2Y6HdZWa6YUMhhP0/31Kyeug4SdxondtJ3MnAfzyUeX7zvnSIirqzNQ11X276rmA+CevWk+eh704AUjgCr4FBOPugllOAzAEAa63D6C6vOI4CSWwXOAhC8KELvc2+ABJnc1hCBi2FX6GqIJpBM4hUSGgwQyjNlYQl3gwciFFEYUE6VFmbgLYuCq83MBIQiqngyOMFH+CAlDPnLVNbjYUwHfhzDGHlXgACfKODiMAUM7WHNxFEGXoXO1wYMUjSIFOocBjpU1CBQJWmQMgRAy9Y94GgUYeBIodJGfPcQQVrALN0hESIkpMRSBfe0xwV7iMkGJMsRFjMitVyssfAhYQhQMRYiOFYjJzwE4VykewciDLQiS9nQFrAw5EsuSgDGIvAAOAWGXsfzXMW2FwZgyQJDvtxqQCNh1JEHyAV4gyuie1l2NdBhFSThUVjSBl0MMiMzvbSYrSDRK1KQIYDRSUioEE6gAsTyPMK15laW5DIogATKCgolPRMZCgqK7YJHbyzlX+hBkBTtWGOIkksUOY1mbcJXX2XU6+yyhEWSiuE2EFCyRlxBhRGGwTh0JjoW4LXXjtBbrLWQMkhBEUJlVHADmYxKq4OUQuBMFTOhECeB1zdhJVIwJA8NAmRWm7aSMsVo2bxEq8sKV3d8movaq5YVAHwVBaygimAIUthKViGokuI3cfXCLSuoSKEo1eqfA0E2ImMw6L6OR0iTqM1UgmTc6BkqOCUQdeIziWPT9oZ0XNPuPy8AkkZ9jtXjRtSIyItbrgQBHyrAMikwmLBnCndWXssRDWIEuFfppErfGE/0qaStK5hCL3R1Fyhjs27W8viSWTwYJVavCORCbrYyIIFKD7RwHrOWJgySUoSnylKlOERGEvn9zdSbgI5qt9+y+ruuGbhrGwBS/Bqxafd/ODnQ3tu6bjeAgw1Vefh1Y2z6+17onmKFpw/JGKz9qhqnFvJhl+XLgbHt+u0qLOqu/HYXBUZVeL7Kab8ext+Nswmi3nUxLo6N3O1aDyMO03Lg/SctpWc4H3XsF7ze9OwuzItPooqzqJk2y0LZjTPTOoHffHB8cSg8YExMqPuV80NkOQBfpAzrJ5pZ5uar4fEdUbdSWabzPqLsS8pMDwXSM5wTC2v15Sf0Yxc1ux1lL3jmR5GN+k34qizRvhOapNF8z9pKa24Zu95+e/Bv342nu7NO8p9+meffkmh8pz1tVXWbsRTT/wwzmfCbOO7M01wKezhtReKKN9JLn6yPHvbR3qu/7u9617I7dMZyebTmHp5jk/bwMe1mTFvIa+Ce0V+Z+YD+9ZDzHdNPq26YtTju2eawCcNtb82R11F30kUb4rKzmWfFJC/k8S39tFf4Ee2XulblX5n4XOz5zjm7ojuN0+7qtO4ff2snuB+5EYTZb8Dwrmq926We1ygeGdZyrvKMYLcqvmrtIs+IFr+es7vqGwLEMyzR6h78+yO5HOj68ZXDSF2/ItzforBFJh54jLO1Go/W3fNUk+Kj3rKxczRbLWcV5oYHZjC+XZZXx5ekPG3FWfG7cIvzgHbHXc/p6v9vrml3zcJFl9wNFBtfXOW+eBdtpubXQ163Ou/Af0EzjE5wdWal9zGotixdlwTXHbV7B3X1Eetj8sto4t4WWuPrz3kNjHzh2M9Nf5Gy/157O5nT9uaSRGw9n1GPM7ov9zLT7pt5/6qeLtnnkryGLRFyFz6iLv6oG+8zqdsThoGMLdaz+o08WPv+SSqVnxIs5r/b0euiBI70J0yptitAubG6eLpfZbO9989HN1r/kfzeC+FHVDKYrbc61OK0+rr68+IdCZ/2NzX7yt4KN+297k3l/xZ8ua57vveHfrPMxG8z7/wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t, Column31 = _t, Column32 = _t, Column33 = _t, Column34 = _t, Column35 = _t, Column36 = _t, Column37 = _t, Column38 = _t, Column39 = _t, Column40 = _t, Column41 = _t, Column42 = _t, Column43 = _t, Column44 = _t, Column45 = _t, Column46 = _t, Column47 = _t, Column48 = _t, Column49 = _t, Column50 = _t, Column51 = _t, Column52 = _t, Column53 = _t, Column54 = _t, Column55 = _t, Column56 = _t, Column57 = _t, Column58 = _t, Column59 = _t, Column60 = _t, Column61 = _t, Column62 = _t, Column63 = _t, Column64 = _t, Column65 = _t, Column66 = _t, Column67 = _t, Column68 = _t, Column69 = _t, Column70 = _t, Column71 = _t, Column72 = _t, Column73 = _t, Column74 = _t, Column75 = _t, Column76 = _t, Column77 = _t, Column78 = _t, Column79 = _t, Column80 = _t, Column81 = _t, Column82 = _t, Column83 = _t, Column84 = _t, Column85 = _t, Column86 = _t, Column87 = _t, Column88 = _t, Column89 = _t, Column90 = _t, Column91 = _t, Column92 = _t, Column93 = _t, Column94 = _t, Column95 = _t, Column96 = _t, Column97 = _t, Column98 = _t, Column99 = _t, Column100 = _t, Column101 = _t, Column102 = _t, Column103 = _t, Column104 = _t]),
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"Hi dufoq3
Don't worry about it. I hope you had a really nice holiday.
I've been extremely busy since coming back off holiday on 13/01 and have not had time to post here or do much more homework.
I have another project on at the moment which is taking all my time but also need to tap you up for some advice on another query I have in mind when I can find time to think about it.
I'd even forgot how to access Advanced Editor until I realised I had to open up a new document and get data...