Forum Discussion
Merging Columns dynamically
- 6 years ago
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 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.
- Query_Addict6 years agoFrequent Visitor
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.
- parry2k6 years ago
Super User
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.