Forum Discussion
Return all values appearing in a given column
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.
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.
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"
11 Replies
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 __ReturnGreg_Deckler this is DAX or M, I try to apply this codes in M and doesn't work, do you can help me?
VRMENDESadq Sorry, that was DAX. Wasn't paying attention.
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.
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.
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"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.
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"
