Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
VRMENDESadq
Frequent Visitor

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: 

 

ProdutoOrigemCustom Colunm
1AA,B
1BA,B
2AA,B,C
2BA,B,C
2CA,B,C
3AA,B
3BA,B
4AA

 

Anybody can help me?

 

Tks.

2 ACCEPTED SOLUTIONS
Lixin
New Member

Firstly, I duplicated the Table and named it Table2.

Lixin_0-1726585714381.png

 

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:

Lixin_1-1726585808611.png

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

 

View solution in original post

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"

View solution in original post

11 REPLIES 11
Ahmedx
Super User
Super User

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"
Lixin
New Member

Firstly, I duplicated the Table and named it Table2.

Lixin_0-1726585714381.png

 

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:

Lixin_1-1726585808611.png

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.

Greg_Deckler
Super User
Super User

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.