Concatenate string from relating rows

[Anonymous]

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-Paris
Travel2	Berlin-Amsterdam-Brussels

Raw data table

Road trip ID	Stop index	Stop place
Travel1	1	Milano
Travel1	2	Geneva
Travel1	3	Paris
Travel2	1	Berlin
Travel2	2	Amsterdam
Travel2	3	Brussels

How can I create the descirption string in the summary table?

Thank you for your support in advance!

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculcated table.

Row data table =
SUMMARIZE(
    'Table',
    'Table'[Road trip ID],
    "Description string",CONCATENATEX(FILTER('Table','Table'[Stop index]<='Table'[Stop index]),'Table'[Stop place],"-"))

2.  Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88 · Accepted solution]

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

[Anonymous]

Thank you very much!

[AlB]

Hi @Anonymous 

I assume you have the summary table in place with the Road trip ID column. If so:

Description string =
CONCATENATEX (
    CALCULATETABLE (
        DISTINCT ( RawTable[Stop place] ),
        FILTER (
            ALL ( RawTable[Road Trip ID] ),
            RawTable[Road Trip ID] = SummaryTable[Road Trip ID]
        )
    ),
    RawTable[Stop place],
    "-"
)

This can also be done in Power query, and it would be probably better.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[camargos88]

@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]

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!