Forum Discussion
Solved
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,...
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"
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"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"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"