Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Concatenate string from relating rows

Dear All, I have a use case where I would like to create description strings for a travel company about road trips.   Summary table: Road trip ID Description string Travel1 Milano-Geneva-...
Need Help
  • camargos88's avatar
    camargos88
    5 years ago

    Anonymous ,

     

    Check this new code:

    Summary = 
    ADDCOLUMNS(
        VALUES('Table'[Road trip ID]), 
        "Desc", CONCATENATEX(FILTER('Table', 'Table'[Road trip ID] = EARLIER('Table'[Road trip ID])), 'Table'[Stop place], "-", 'Table'[Stop index], ASC) 
    )

     

    Concatenex has a sort by parameter.

camargos88's avatar
camargos88
Community Champion
5 years ago

Anonymous ,

 

You can use this mcode:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKLEvNMVTSUQJh38ycxLx8pVgdZAkjIHZPzUstS0STMAbigMSizGIkcSOoSU6pRTmZeWgSIOyYW1ySWpSSmIsmBzLMqai0uDg1B2heLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Road trip ID" = _t, #"Stop index" = _t, #"Stop place" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Road trip ID", type text}, {"Stop index", Int64.Type}, {"Stop place", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Road trip ID"}, {{"Rows", each Table.Sort(_, "Stop index"), type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Rows][Stop place], "-")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", type text}})
in
    #"Changed Type1"

 

 

 

 

This is a DAX solution:

 

Summary = ADDCOLUMNS(VALUES('Table'[Road trip ID]), "Desc", CONCATENATEX(FILTER('Table', 'Table'[Road trip ID] = EARLIER('Table'[Road trip ID])), 'Table'[Stop place], "-"))
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi camargos88 ,

     

    I tried your proposed DAX and it works almost perfectly. However it does not consider the stop index, which determines the sequence of the locations in the string.

     

    Do you have any idea how to adjust the DAX?

     

    Thank you very much!