Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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.
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"
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"
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 @Lixin , 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.
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.
@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
@Greg_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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |