Concatenate text in different row

[XaviOV]

Hi,

I have a table with values that I need concatenat by number of Year-WeekOfYear. Agree a example.

Original:

AñoSemana	Filtros preparación	Índice
2019-S3	L1	1
2019-S3	L1	2
2019-S4	L7	3
2019-S4	L7	4
2019-S4	L7	5
2019-S4	L1	6
2019-S5	L6	7
2019-S5	L6	8

Result:

AñoSemana	Filtros preparación	Índice	Filtros preparación Semana
2019-S3	L1	1
2019-S3	L2	2	L1, L2
2019-S4	L7	3
2019-S4	L4	4
2019-S4	L6	5
2019-S4	L1	6	L7, L4, L6, L1
2019-S5	L5	7
2019-S5	L6	8	L5, L6

Regards,

Xavi

[v-kelly-msft]

Hi @XaviOV ，

Create a calculated column as below:

Filtros preparación Semana = 
var _maxvalue=MAXX(FILTER('Table','Table'[AñoSemana]=EARLIER('Table'[AñoSemana])),'Table'[Índice])Return
IF('Table'[Índice]=_maxvalue,CONCATENATEX(FILTER('Table','Table'[AñoSemana]=EARLIER('Table'[AñoSemana])),'Table'[Filtros preparación],","),BLANK())

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AlB · Accepted solution]

@XaviOV 

Calculated column in the first table:

Calc Column =
VAR last_ =
    CALCULATE ( MAX ( Table1[Índice] ), ALLEXCEPT ( Table1, Table1[AñoSemana] ) )
RETURN
    IF (
        Table1[Índice] = last_,
        CONCATENATEX (
            CALCULATETABLE (
                DISTINCT ( Table1[Filtros preparación] ),
                ALLEXCEPT ( Table1, Table1[AñoSemana] )
            ),
            Table1[Filtros preparación],
            ", "
        )
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

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

Cheers 

[AlB]

@XaviOV 

Can also be done in the query editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQNNlbSUfIxBBKGSrE6KIJGQMIISdAEJGgOJIzRBUGECbqgGZAwRRcEWWSGJGgKEgQR5uiCIO0WSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AñoSemana = _t, #"Filtros preparación" = _t, Índice = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AñoSemana", type text}, {"Filtros preparación", type text}, {"Índice", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(Table.SelectRows(#"Changed Type", (inner)=> inner[AñoSemana] = [AñoSemana])[Filtros preparación], ", ")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "isLast", each if List.Max(Table.SelectRows(#"Changed Type", (inner)=> inner[AñoSemana] = [AñoSemana])[Índice]) = [Índice] then 1 else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each if [isLast]=1 then [Custom] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "isLast"})
in
    #"Removed Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

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

Cheers