Concatenating text items into sequences based on previous rows

[MagnusJ]

I have a problem that I have been attempting to solve through various approaches but it is slightly too complex for me. Not even sure if the best solution is through DAX or by using Power Query.
Power Query has at least helped me reduce the complexity of the data set but the last step is difficult. This is a problem that would be quite simple in excel but I can not solve it through DAX or in Power Query.

I have a table with customers with numbered identities and for each customer an ownership of a product (in the below example a car brand), represented by a text string. I need to create sequences of ownership, i.e. how the customer switches from one brand to another in order to later on calculate how many customers display a certain switching pattern (the image is from an excel sheet to simplify the data set). All duplicates have been removed, i.e. rows for subsequent periods with the same brand ownership for a customer have been removed through filtering.

One solution would be to create a new table but that might not be neecessary as long as the number of customers can be counted in a later step (e.g. through DISTINCTCOUNT). And since this problem requires combining/concatenating text strings it is as far as I know not easily solved by DAX functions through creating a new table.

Any ideas on how to solve this are much appreciated.

/Magnus

Customer number	Brand	Sequence		New table?	Customer	Sequence
1	Ford				1	Ford->Nissan
1	Nissan	Ford->Nissan			5	Nissan->Toyota->Mercedes
5	Nissan				10	BMW->Mercedes
5	Toyota				25	Nissan->Toyota->BMW->Mercedes
5	Mercedes	Nissan->Toyota->Mercedes			31	Ford->Nissan
10	BMW
10	Mercedes	BMW->Mercedes
15	Toyota
25	Nissan
25	Toyota
25	BMW
25	Mercedes	Nissan->Toyota->BMW->Mercedes
31	Ford
31	Nissan	Ford->Nissan

[MagnusJ]

Excellent solutions! Thank you! I went with the summarizecolumns-solution to be able to use the concatenated strings as an axis in a chart or as a legend.  Power Query-solution would also be possible of course. All in all an interesting problem with what looks like simple solutions once you see it, but it was impossible for me to crack. Expertise was needed! Thanks!

[MarcelBeug]

In Power Query you can create base code with the "Group By" option on the Transform tab and adjust the generated code to combine the texts as ilustrated in this video. It is recorded with Excel, but can be used in Power BI as well.

Below the generated code. I just noticed after recording that you don't need single values.

These can be filtered out by adding additional aggregation "Count Rows" with "Group By" and filter out the 1's afterwards.

Or keep the recorded solution and add a step to filter out Sequences that don't contain " -> ".

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer number", Int64.Type}, {"Brand", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer number"}, {{"Sequence", each Text.Combine([Brand]," -> "), type text}})
in
    #"Grouped Rows"

[Sean · Accepted solution]

@MagnusJ

I'm sure what @MarcelBeug suggests works!

However you don't need to create another table to achieve this

You just need a single Measure

Sequence MEASURE =
IF (
    HASONEVALUE ( 'Table'[Customer number] ),
    CONCATENATEX ( 'Table', 'Table'[Brand], "-> " )
)

Here's the result

If you do want to create another table on the Modeling tab click New Table and type...

Summary Table =
SUMMARIZECOLUMNS (
    'Table'[Customer number],
    "Sequence of Ownership", CONCATENATEX ( 'Table', 'Table'[Brand], "-> " )
)

Here's this result...

Hope this helps!