Concatenate string from relating rows

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], "-"))

 

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 

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.