Forum Discussion
Merging Columns dynamically
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.⚡
Microsoft EmployeePlease 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
- parry2k
Super User
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.
- parry2k
Did you tried the solution I posted, it takes care of both the points you raised.
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.
