Forum Discussion

Burubear's avatar
Burubear
Icon for Helper I rankHelper I
6 years ago
Solved

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 ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

Smauro's avatar
Smauro
Icon for Solution Sage rankSolution Sage
6 years ago

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