Return all values ​​appearing in a given column

[VRMENDESadq]

HI, I need to create a custom column that shows all the values ​​in the "Origem" column according to the value in the "Produto" column, for example: 

Produto	Origem	Custom Colunm
1	A	A,B
1	B	A,B
2	A	A,B,C
2	B	A,B,C
2	C	A,B,C
3	A	A,B
3	B	A,B
4	A	A

Anybody can help me?

Tks.

[Anonymous · Accepted solution]

Firstly, I duplicated the Table and named it Table2.

In Table, this is the M-Code to transform the column to the custom column that you want:

= Table.Group(Source, {"Produto"}, {{"Custom Column", each Text.Combine([Origem], ";"), type text}})

This is the result:

Then I use merge function in PQ to add custom column to Table2 to get the final result.

[Ahmedx]

pls try code in M

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCO4mBGKmDOYZQyXNYbLmkDEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Produto = _t, Origem = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Produto", Int64.Type}, {"Origem", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine( Table.SelectRows( #"Changed Type",(x)=>x[Produto]=[Produto])[Origem],","))
in
    #"Added Custom"

[VRMENDESadq]

Hi @Ahmedx 

Its work, but in example just have a little piece of data in colunm Produto e Origem, do you know any code that I can apply  this logic in a column that has a lot of data?

Thanks.

[Ahmedx]

this should work even faster

let


lst = List.Buffer( Table.ToList( from,(x)=>x)),
f= (w)=> Text.Combine(List.Transform(List.Select(lst,(x)=> x{0}=w),(x)=> x{1}),", "),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCO4mBGKmDOYZQyXNYbLmkDEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Produto = _t, Origem = _t]),
    from = Table.TransformColumnTypes(Source,{{"Produto", Int64.Type}, {"Origem", type text}}),
    #"Added Custom1" = Table.AddColumn(from, "Custom", each f([Produto]))
in
    #"Added Custom1"

[Ahmedx]

lst = List.Buffer( Table.ToList( from,(x)=>x)),
f= (w)=> Text.Combine(List.Transform(lst,(x)=> if x{0}=w then x{1} else null),", "),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCO4mBGKmDOYZQyXNYbLmkDEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Produto = _t, Origem = _t]),
    from = Table.TransformColumnTypes(Source,{{"Produto", Int64.Type}, {"Origem", type text}}),
    #"Added Custom1" = Table.AddColumn(from, "Custom", each f([Produto]))
in
    #"Added Custom1"

[Ahmedx · Accepted solution]

pls try again

let


lst = List.Buffer( Table.ToList( from,(x)=>x)),
f= (w)=>  Text.Combine( List.Transform(List.PositionOf( List.Transform(lst,(z)=>z{0}),w,Occurrence.All), (x)=> lst{x}{1}?),", "),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzALCO4mBGKmDOYZQyXNYbLmkDEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Produto = _t, Origem = _t]),
    from = Table.TransformColumnTypes(Source,{{"Produto", Int64.Type}, {"Origem", type text}}),
    #"Added Custom1" = Table.AddColumn(from, "Custom", each f([Produto]))
in
    #"Added Custom1"

[Anonymous · Accepted solution]

Firstly, I duplicated the Table and named it Table2.

In Table, this is the M-Code to transform the column to the custom column that you want:

= Table.Group(Source, {"Produto"}, {{"Custom Column", each Text.Combine([Origem], ";"), type text}})

This is the result:

Then I use merge function in PQ to add custom column to Table2 to get the final result.

[VRMENDESadq]

Hi @Anonymous , this step doesn't work: 

Table.Group(Source, {"Produto"}, {{"Custom Column", each Text.Combine([Origem], ";"), type text}})

when I add the custom colunm and put this code, PQ returns: Expression.Error: The name 'Source' was not recognized. Make sure it is spelled correctly.

[Anonymous]

Hi,

The first parameter is the table name. Here, it refers to the name of the previous step. For me, my previous step is called "Source". You can check yours on the right side of your screen. The steps are cut off from my screenshots.

[Greg_Deckler]

@VRMENDESadq Try:

Column =
  VAR __Produto = [Produto]
  VAR __Table = FILTER( 'Table', [Produto] = __Produto )
  VAR __Return = CONCATENATEX( __Table, [Origem], "," )
RETURN
  __Return


Measure =
  VAR __Produto = MAX([Produto])
  VAR __Table = FILTER( ALLSELECTED('Table'), [Produto] = __Produto )
  VAR __Return = CONCATENATEX( __Table, [Origem], "," )
RETURN
  __Return

[VRMENDESadq]

@Greg_Deckler this is DAX or M, I try to apply this codes in M and doesn't work, do you can help me?

[Greg_Deckler]

@VRMENDESadq Sorry, that was DAX. Wasn't paying attention.