Forum Discussion
Merging Columns dynamically
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
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.⚡
7 Replies
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 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.⚡
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.
Thanks for the help, I now understand the value of adding a custom list - big step forward in my M query education.
Did you tried the solution I posted, it takes care of both the points you raised.
