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 a column that returns all the values ​​that appear in the "Class" column according to the "City" column, for example:

CityClassResult
New YorkAA,B,C
New YorkBA,B,C
New YorkCA,B,C
New JerseyAA,B
New JerseyBA,B
DenverAA,B,C,D
DenverBA,B,C,D
DenverCA,B,C,D
DenverDA,B,C,D
HonoluluAA

 

Anybody can help me?

 

tks.

1 ACCEPTED SOLUTION

If you enter that code, by itself, into the Advanced Editor, it will reproduce your data table in your original post, (at the Source step), as well as the final result (at the last step).

 

If you look at your own query, you will probably also see a Step named "Source" which is where your data comes from.

 

If your data has the same structure as the sample in your original post, all you should need to do is replace one Source with the other (in the Advanced Editor).

View solution in original post

8 REPLIES 8
Omid_Motamedise
Memorable Member
Memorable Member

Use this formula:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Distinct(Table.SelectRows(Source,(x)=>x[City]=[City])[Class]),","))
in
    #"Added Custom"

@Omid_Motamedise 

Doesn't work, 

In my case I just need create a custom colunm not a table, do you can help me?

 

OBS: reinforcing that this example is just a slice of the data colunm, I have a much data in colunm City and Class, I just need a custom colunm with the logic.

ronrsnfld
Super User
Super User

You can use the Table.Group method.

 

If you just need a single line per city:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lFyVIrVQRFwQhdwhgt4pRYVp1aiaIILQbS5pOaVpRbBVcC5aLLOqFwXMNcjPy8/pzSnFKI7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Class = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Class", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {
        {"Class", each Text.Combine([Class],","), type text}})
in
    #"Grouped Rows"

 

ronrsnfld_0-1726532195934.png

 

If you want the table expanded as you show in your question:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lFyVIrVQRFwQhdwhgt4pRYVp1aiaIILQbS5pOaVpRbBVcC5aLLOqFwXMNcjPy8/pzSnFKI7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Class = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Class", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {
        {"Add Class", (t)=>Table.AddColumn(t, "Result", each Text.Combine(t[Class],",")),
            type table [City=text, Class=text, Result=text]}}),
    #"Expanded Add Class" = Table.ExpandTableColumn(#"Grouped Rows", "Add Class", {"Class", "Result"})
in
    #"Expanded Add Class"

ronrsnfld_1-1726532415596.png

 

 

 

Hi, @ronrsnfld 

 

In my case I just need create a custom colunm not a table, do you can help me?

 

OBS: reinforcing that this example is just a slice of the data colunm, I have a much data in colunm City and Class, I just need a custom colunm with the logic.

The code works on the data you presented.

You should be able to adapt one of the answers to your actual data.

 

And either solution should work no matter how much data you have in your City and Class columns.

 

If your data is truly representative, then you probably have adapted it incorrectly, but you present no information to enable troubleshooting that situation.

 

If your data is not truly representative, then GIGO.

@ronrsnfld 
Where do I insert the code below? in a custom column or enter a step manually?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lFyVIrVQRFwQhdwhgt4pRYVp1aiaIILQbS5pOaVpRbBVcC5aLLOqFwXMNcjPy8/pzSnFKI7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Class = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"City", type text}, {"Class", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {
{"Add Class", (t)=>Table.AddColumn(t, "Result", each Text.Combine(t[Class],",")),
type table [City=text, Class=text, Result=text]}}),
#"Expanded Add Class" = Table.ExpandTableColumn(#"Grouped Rows", "Add Class", {"Class", "Result"})
in
#"Expanded Add Class"

If you enter that code, by itself, into the Advanced Editor, it will reproduce your data table in your original post, (at the Source step), as well as the final result (at the last step).

 

If you look at your own query, you will probably also see a Step named "Source" which is where your data comes from.

 

If your data has the same structure as the sample in your original post, all you should need to do is replace one Source with the other (in the Advanced Editor).

Thanks its work!!

You are monster of PBI!!

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.