Merging different columns using line breaks and "-"

[Burubear]

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

[v-alq-msft]

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.

[Burubear]

sorry not that advance yet in codings and using advanced query editor, would you show which formula used/ buttons clicked  in each step

[v-alq-msft · Accepted solution]

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.

[Burubear]

thank you so much! it's now easier to understand for new PBI users like me. Works really well and got the result that I wanted!

[Smauro]

@Burubear 

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