Transforming Data within adjacent Column Headers into one cell and populating that cell...

[Rhothgar]

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.

[dufoq3 · Accepted solution]

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

[dufoq3]

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.

[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...

[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.

[dufoq3]

@Rhothgar,

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"

[Rhothgar]

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... 

[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!!!

[dufoq3]

It works that way (both versions):

Source:

v1:

v2:

[Rhothgar]

V1 looks great.  I must have missed something.

I've done so much work today on this that I am becoming afraid of changing things.  I think this is probably a natural reaction of a beginner.

[dufoq3]

If you are afraid of changing things - just open Advanced editor and copy all your code. Paste it into the notepad and save 🙂

BTW I recommend using notepad++ with custom language formating (like this)

You can build M language for notepad++ with this tutorial or you can use my languages here.

[Rhothgar]

Ended up doing it manually in the end.

Still got syntax errors though.

Expression.SyntaxError: Token ',' expected.

However,  every "," seems to be in place but it doesn't resolve.

[dufoq3]

I've just edited Replaced Value step (it is dynamic now). To make it dynamic I've changed hardcoded values in {} to Table.ColumnNames(Source)

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 colName & "|" & Text.BeforeDelimiter(_, ":"), type text}
)),
#"Merged Columns" = Table.CombineColumns(TransformedColumns,{"GTIN", "COLOUR", "MAKE", "GENDER", "Model"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Attribute")
in
#"Merged Columns"

 Result:

If you want to merge all columns except blank ones

Use this code:

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"

[Rhothgar]

That bottom code is superb.  Thank you very much.

I am also trying to set up another query that I spent all day on yesterday and I thought it looked so simple.

Basically, same thing. One column in this instance but need to change colon to a semi-colon.

There are a variable number of : delimiters in each cell.  Some cells have none. Some cells have one, some have three, some might have nineteen.

Again, same spreadsheet so I need to have blank cells to maintain cut and paste capabilities on a different worksheet.

I hope I am compiling this spreadsheet correctly.

I want to end up effectively with a dynamic source ie. I download a file with all these columns in, drop it into a set folder where it should stay and then open this spreadsheet where all the transformation occurs.

So in the spreadsheet I expect to have quite a few queries created using Power Query from which I then go to a certain worksheet to compile the output CSV file simply by using = in the relevant cells to point to the relevant queries.

Does that make sense?

[dufoq3]

Create an examle in Power Query and paste whole code here please. We can discuss how to solve it afterwards. Paste here also picture of expected result.

[Rhothgar]

Hi

I've just deleted and started again.

Without using code, how do I actually select the Attribute: row so that I can select Replace Values.

When I did this, it created the same as your code BUT instead of Column1, etc it was the name of the attribute.

My selection techniques need sharpening as does my understanding.

I'll create another query now and look at the above code.

Thanks.

EDIT: Also is it easy to combine multiple tables as there is this 3000 cell limit so I can only do 28 lines at a time longhand...

[dufoq3]

Normaly yo can not select only one row in table. If you want to do transformation you can do it on whole column only (but of course you can set conditions). Paste whole code here if you need help.

[Rhothgar]

Do you mean paste the WHOLE spreadsheet?

It's so large you wouldn't be able to see the outputs required.  There 306 columns in the source file and only 77 in the output file of which perhaps 16 - 20 might be populated from the 306 columns?

[dufoq3]

No, just create sample data with ENTER DATA button and paste here M code from Advanced Editor

[Rhothgar]

Hi dufoq3,

I have come back to this post to read up on how to create sample data but I do not seem to have ENTER DATA button in PowerQuery or Excel.

I keep reading until I work out how to do this. I failed to do it right I think when I followed your "Check this link if you don't know how to provide sample data".  I simply cannot understand what I am doing wrong.

[dufoq3]