Forum Discussion
Merging different columns using line breaks and "-"
Hi All,
I've search multiple responses in this forum and it seems like they can't solve the result I wanted (hopefully I've searched enough, sorry about that if this is already answered)
I have multiple columns with phrases which are updates made by different teams so there would be rows with nulls and blanks on them. I was wondering if there would be a formula or power query way to merge the columns wherein they can merge these phrases with line breaks as well as starting each phrases that are not null with "-"
Sample table would be below:
| Team A | Team B | Team C | Result Column |
| null | null | null | |
| null | |||
| Company B will join the team meeting | All employees will agree on clocking in and out | -Company A will join the Company -All employees will agree on clocking in and out | |
| Company A will join the Company this June | Every employee is on Leave | null | -Company A will join the Company this June -Every employee is on Leave |
| null | null | null |
Hi, Burubear
Here is the table. The pbix file is attached in the end.
Table:
You may go to 'Add Coumn' ribbon, click 'Custom Column', paste the following codes in the 'Custom column formula'.
=let teama = if [TeamA]="" then "" else "-"&[TeamA], teamb = if [TeamB]="" then "" else "-"&[TeamB], teamc = if [TeamC]="" then "" else "-"&[TeamC], list = List.Select({teama,teamb,teamc},each _<>"") in Text.Combine(list,"#(lf)")Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi, Burubear
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Here are the codes in 'Advanced Editor'.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5BCoNADEWv8nHtJbR0U3oDcTHYj06dSURHy9y+YYpdCFmE/JeXdF1V1aX6+treNC5OMlp8fAh4qxekiUh0EZFMXkbDGssYl6CZ3H6oG1cSKhiCDrNhsFUnL+ieiv1UNxf1OU+T3/DYhea/H1zz/wIsMPGT7mD5tf8C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TeamA = _t, TeamB = _t, TeamC = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TeamA", type text}, {"TeamB", type text}, {"TeamC", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let teama = if [TeamA]="" then "" else "-"&[TeamA], teamb = if [TeamB]="" then "" else "-"&[TeamB], teamc = if [TeamC]="" then "" else "-"&[TeamC], list = List.Select({teama,teamb,teamc},each _<>"") in Text.Combine(list,"#(lf)") ) in #"Added Custom"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sorry not that advance yet in codings and using advanced query editor, would you show which formula used/ buttons clicked in each step
Hi, Burubear
Here is the table. The pbix file is attached in the end.
Table:
You may go to 'Add Coumn' ribbon, click 'Custom Column', paste the following codes in the 'Custom column formula'.
=let teama = if [TeamA]="" then "" else "-"&[TeamA], teamb = if [TeamB]="" then "" else "-"&[TeamB], teamc = if [TeamC]="" then "" else "-"&[TeamC], list = List.Select({teama,teamb,teamc},each _<>"") in Text.Combine(list,"#(lf)")Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You could use a custom function:
fnMergeComments = (l as list) as nullable text => let FixList = List.Accumulate( l, "", (s, c) => if (c??"") = "" then s else s & "#(lf)- " & c ), FixStart = if FixList = "" then null else Text.AfterDelimiter(FixList, "#(lf)") in FixStart, #"Merge Comments" = Table.CombineColumns(PreviousStep ,{"Team A", "Team B", "Team C"},fnMergeComments,"Comments") in #"Merge Comments"where PreviousStep is your last query step.
Best,
Spyros
