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