Forum Discussion

MagnusJ's avatar
MagnusJ
Frequent Visitor
9 years ago
Solved

Concatenating text items into sequences based on previous rows

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. Pow...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    MagnusJ

    I'm sure what MarcelBeug suggests works! :smileyhappy:

    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! :smileyhappy:

     

     

     

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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's avatar
Sean
Community Champion
9 years ago

MagnusJ

I'm sure what MarcelBeug suggests works! :smileyhappy:

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! :smileyhappy: