Forum Discussion
Rhothgar
Helper IV
Helper IVTransforming 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.
Basically, and please bare with me as I probably don't understand the correct terms to use, I need to concatenate the word "Attribute:" from every column header and use the remaining text to form the data I need.
The CSV has 104 Column Headers which start "Attribute:" The word following the colon is a KEY e.g. GTIN; BRAND; SIZE; WHATEVER;
In each row below the header there is an attribute VALUE followed by a CHECKBOX which should be set to Visible or not.
Here are some examples:-
So I need to end up with one cell under a column called Attribute which is formatted thus:-
"GTIN | 4084900010211; MODEL | Orlow; TYPE | Graphite Pencils; MAKE | Lyra; QUANTITY | 1 Box of 12 Pencils"
"GTIN | 501055579405; MAKE | Michelin; MODEL | L13;"
I hope you get the idea.
I think the visibility can probably be managed internally in the webhost UI though not sure at present.
I really hope someone will say that this is really easy and this is how you do it!!!
Right now none of it makes sense.
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.
46 Replies
- dufoq3
Community Champion
In general I can recommend this course by Leila Gharani. It will not teach you everything but it is realy well explained with many examples and use cases. Leila is great instructor and this course helped me a lot. After this course you'll be able to help yourself a lot.
jgeddesSuper User
I created a small table that I think mimics your data.
I was able to get the following result...
with the following code...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwpKcpMKi1JtXIP8fRT0kEScHRyRuH7Jmanogi4uLqhKshPSc1RitWJVjIxsDCxNDAwsDIEESFFpXDjgcg3scI3sSjRygguBzUZiJxzEouLM5OtTBFyxBkKRUSq883My7QywWWHiamBlTEWvY4FBTmpyNrgzlaKjQUA", 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]), #"Removed Top Rows" = Table.Skip(Source,1), #"Added Index" = Table.AddIndexColumn(#"Removed Top Rows", "Index", 1, 1, Int64.Type), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Columns", {{"Value", each if Text.Length(_) > 0 then Text.AfterDelimiter(_, ":", {0, RelativePosition.FromEnd}) & "|" & Text.BeforeDelimiter(_, ":") else null, type text}}), #"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index"}, {{"Attribute", each Text.Combine(_[Value], ";"), type text}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"}) in #"Removed Columns"You can paste the code into the Advanced Editor of a blank query and step through the process.
Hope this gets you pointed in the right direction.
RhothgarWell that has just blown my mind!
Never in a million years would I have been able to write a solution. This is where you modestly tell me that it took you 5 minutes...
It truly is mind blowing how powerful Power Query is.
I've not tried it yet but am about to. I'd be guessing but will I need to expand on from Column5 = _t upto to Column104 = _t ?
Honestly, I am laughing here in disbelief at how amazing this is.
How many years does it take to learn this from scratch?
All I have to do now is understand the blank query bit... 🙄
- Rhothgar
OK. So I have had a play and have already learned how to create a new query and I can now get the same result as you.
The stepping in the query really helps with basic understanding of what each line does.
However, when I add "Column6 = _t" I get an error. Without me adding anything, everything is fine but I do not understand how it compiles the column as you appear to make NO reference to the Columns in your code?
And so I have some more questions. This could be a rabbit hole...
I note that the GTIN are truncated to 8 integers. Is this as a result of the int64, 64 bits divided by 8? I am clutching at straws here.
What does this do please?i45WciwpKcpMKi1JtXIP8fRT0kEScHRyRuH7Jmanogi4uLqhKshPSc1RitWJVjIxsDCxNDAwsDIEESFFpXDjgcg3scI3sSjRygguBzUZiJxzEouLM5OtTBFyxBkKRUSq883My7QywWWHiamBlTEWvY4FBTmpyNrgzlaKjQUA
How is this compiled please? If I delete it from your code then it fails with an error so I am guessing again that all the data I presented you somehow encoded into the string?
OK! Scrub that!
Your reply is actually detailed if only I took the time to read it slowly!!!
You say you have mimicked it by creating a table so that explains why the data is truncated I would think.
So I am now going to have a play around with creating a table. I've absolutely no idea whether I do that in Excel or in Power Query but there is no harm in trying myself now that you have given me some knowledge.
They do say that a little knowledge is a dangerous thing...- dufoq3
Hi Rhothgar ,
I've used jgeddes data sample.
Here you have same result but with blank columns included:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwpKcpMKi1JtXIP8fRT0kEScHRyRuH7Jmanogi4uLqhKshPSc1RitWJVjIxsDCxNDAwsDIEESFFpXDjgcg3scI3sSjRygguBzUZiJxzEouLM5OtTBFyxBkKRUSq883My7QywWWHiamBlTEWvY4FBTmpyNrgzlaKjQUA", 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]), #"Replaced Value" = Table.ReplaceValue(Source,"Attribute:","",Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4", "Column5"}), #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [PromoteAllScalars=true]), TransformedColumns = Table.TransformColumns( #"Promoted Headers", List.Transform(Table.ColumnNames(#"Promoted Headers"), (colName)=> {colName, each colName & "|" & Text.BeforeDelimiter(_, ":"), type text} )), #"Merged Columns" = Table.CombineColumns(TransformedColumns,{"GTIN", "ABC", "Make", "DEF", "Model"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Attribute") in #"Merged Columns"Here you have another example with same result as jgeddes
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwpKcpMKi1JtXIP8fRT0kEScHRyRuH7Jmanogi4uLqhKshPSc1RitWJVjIxsDCxNDAwsDIEESFFpXDjgcg3scI3sSjRygguBzUZiJxzEouLM5OtTBFyxBkKRUSq883My7QywWWHiamBlTEWvY4FBTmpyNrgzlaKjQUA", 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]), #"Replaced Value" = Table.ReplaceValue(Source,"Attribute:","",Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4", "Column5"}), #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [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} )), MergedColumns = Table.FromList(Table.ToList(TransformedColumns, each Text.Combine(_, ";"))), #"Renamed Columns" = Table.RenameColumns(MergedColumns,{{"Column1", "Attribute"}}) in #"Renamed Columns"- Rhothgar
Thanks.
If there is a row which is entirely blank, how would I include a placeholder so the end result shows a row which is empty please?
Also, I have manually selected the Source which gives the encoded text but ultimately I will download a new file which will have all the attribute columns in the same place (Columns CA to FZ - 104 columns). The biggest file has 8356 row although currently I have 540 active rows.
I noted yesterday when playing around with tables that there is a limitation of 3000 cells. 540 rows x 104 columns is over 56000 cells!!!
- jgeddes
Glad to hear the solution is helping. Power Query is amazing in what it can accomplish and there are users on this forum that amaze me with their knowledge as well.
If you want to enter a static data table into your model you would do so through the "Enter Data" icon
Once you have entered your data and load it into the model Power Query converts the table to binary Base 64. The "Source" step is converting that binary value (the long nonsense looking string value) back into a usable table.
If you editted the table to include additional rows, Power Query would add the additional columns in type function automatically (the bit at the end with the Column5 = _t stuff)
If you want to use your own data you can replace my Source step with a step that loads in an Excel file or you could use the 'Enter Data' button.Please feel free to send me a direct messafe if you have additional questions.
- Rhothgar
EUREKA!
It took me an age to work out how to change the source. I basically resolved it by clicking on the cog symbol by the side of source and then copied and pasted some different information.
It threw an error as there is a limitation of 3000 cells - there are over 56000 cells which need querying even though the results may be far more limited output than what goes in as there are a lot of empty cells.
So far, I have not worked out how to change the source for a different spreadsheet or worksheet.
I think I will also need a space after each semi-colon too. Not sure if that is a simple fix?
I really pleased with what I've learned today although in real terms I guess it is very little in relation to your knowledge. It's just one or two clicks for you. I guess it is so hard for me to understand as I am simply clicking my way around the UI.
- Rhothgar
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.- dufoq3
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:
- Rhothgar
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.