Merging Columns dynamically

[Query_Addict]

I have a table with a structure similar to the one below.

Product ID	Item Name	Col#1	Col#2	Col#3	Col#4	Col#5
A001	Spoon	Handle:Red	Size:Junior	Made:1989
A002	Knife	Handle:Red		Made:2007	Blade:Serated
A003	Fork	Handle:Metal	Size:Adult	Made:2019		Service:Apex

I want to merge all columns, except the first 2, into a single column, deliminated with a comma.  To make the process future proof, I'm trying to do this without using column names so that the number and name of extra columns can be changed over time. 

The end result, after tidying up columns etc, would look soemthing like.. 

Product ID	Item Name	Tag List
A001	Spoon	Handle:Red,Size:Junior,Made:1989
A002	Knife	Handle:Red,Made:2007,Blade:Serated
A003	Fork	Handle:Metal,Size:Adult,Made:2019,Service:Apex

I'm sure the answer has something to do with column index numbers, its just a bit beyond my skill level at teh moment .

Thanks in advance for your help 

[mahoneypat]

Please put this M code into a blank query to see one way to dynamically merge with commas the 3rd through 7th columns, regardless of column names.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUQouyM/PA9IeiXkpOalWQakpIMHMqlQrr9K8zPwiIM83MSXVytDSwhLIVgBipVgdsHYjINs7LzMtFV27AkyTkYGBOZDtlAPiBKcWJZZA5aEmGAM5bvlF2QgDfFNLEnNgLnBMKc0pQZhlCHMA0KSyzGSgfEFqhVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Item Name" = _t, #"Col#1" = _t, #"Col#2" = _t, #"Col#3" = _t, #"Col#4" = _t, #"Col#5" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", type text}, {"Item Name", type text}, {"Col#1", type text}, {"Col#2", type text}, {"Col#3", type text}, {"Col#4", type text}, {"Col#5", type text}}),
Custom1 = List.LastN(Table.ColumnNames(#"Changed Type"),5),
#"Merged Columns" = Table.CombineColumns(#"Changed Type",Custom1,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Tag List")
in
#"Merged Columns"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Query_Addict]

Thanks for the help, I now understand the value of adding a custom list - big step forward in my M query education.

[parry2k]

Did you tried the solution I posted, it takes care of both the points you raised.

[Query_Addict]

I did, just struggled to understand the code in the last line.  In the end managed to fuse both answers - Used your combine columns and then added a deliminated column based upon the custom field list, before removingthe unwanted columns in the final result.

Not as elegant as your code, but certainly easier to understand in my head. 

Thanks again.     

[parry2k]

Great it worked. The overall  script was very easy and if you need explanation on any part of the script, let me know and I will be more than happy to explain. Seems like you have the somution

in place.

[Query_Addict]

Thanks for this, I almost there - your help with a couple of refinements would help...

1.  The actual querry needs to be able to flex for an unknown number of columns (the first 2 will always be there, but there may be one, or lots, of extra columns).  I've used this code:

Custom1 = List.LastN(Table.ColumnNames(#"Changed Type"),List.Count(Table.ColumnNames(#"Changed Type"))-2), 

Is this the best solution?

2. I want to exclude columns where the content is null.  so that 

Handle:Metal,Size:Adult,Made:2019, ,Service:Apex

becomes:

Handle:Metal,Size:Adult,Made:2019,Service:Apex

I though the answer might be to add a column, as this doesnt seem to add deliminators when the field is null. Then delete the the originals , but the M code seems to get flooded with column names again and I'm not sure this makes point 1. above possible.

[parry2k · Accepted solution]

@Query_Addict although @mahoneypat solution will work and it is a great solution but one challenge I see with this solution is that it will add extra "," commas for blank and null values and also it will only take last 5 columns whereas the following script will ignore first two columns and take all other columns in case it changes in the future and also it will remove null or blank column values and will not add extra commas.

Well done @mahoneypat 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUQouyM/PA9IeiXkpOalWQakpIMHMqlQrr9K8zPwiIM83MSXVytDSwhLIBqFYHbBuIyDbOy8zLRVdN0yLkYGBOZDtlAPiBKcWJZZApKH6jYFst/yibIR239SSxByY9Y4ppTklCKMMobYDzSnLTAZKF6RWKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Item Name" = _t, #"Col#1" = _t, #"Col#2" = _t, #"Col#3" = _t, #"Col#4" = _t, #"Col#5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product ID", type text}, {"Item Name", type text}, {"Col#1", type text}, {"Col#2", type text}, {"Col#3", type text}, {"Col#4", type text}, {"Col#5", type text}}),
    #"Combine Columns" = List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),2),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",#"Combine Columns",(colValues)=>let newColValues=List.RemoveItems(colValues,{"",null}) in Text.Middle(List.Accumulate(newColValues, "", (s, c) => s & "," & c),1) ,"Tag List")
in
    #"Merged Columns"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡