Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
This should be easy to achieve, yet I'm breaking my head over it.
I have a table like this:
id | branche |
101 | business |
101 | hr |
102 | business |
103 | business |
103 | finance |
103 | hr |
I want to combine the branches and make id unique, like this:
id | branche |
101 | business, hr |
102 | business |
103 | business, finance, hr |
Anyone know how to achieve this, or how to get the combined branches in a new column so I can remove dupplicate rows based on id?
Solved! Go to Solution.
Here is the Power Query "M" code version:
let Source = Csv.Document(File.Contents("C:\temp\powerbi\group.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"branche", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Branches", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Branches][branche],",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Branches"}) in #"Removed Columns"
Basically, group the rows but keep all as the aggregation, return a table for "branche". Then, do a text combine on that table to get all of the values formatted as a single list of values with a comma separator.
Here is the Power Query "M" code version:
let Source = Csv.Document(File.Contents("C:\temp\powerbi\group.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"branche", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Branches", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Branches][branche],",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Branches"}) in #"Removed Columns"
Basically, group the rows but keep all as the aggregation, return a table for "branche". Then, do a text combine on that table to get all of the values formatted as a single list of values with a comma separator.
Create a DAX measure like this:
Branches = CONCATENATEX(VALUES('group'[branche]),[branche],",")
Put "id" column and "Branches" measure in a table visualization and set your "id" column to "Do not summarize".
Or, did you want this in an "M" solution?
Good afternoon Greg,
thanks for providing the DAX code, I really appreciate it. In addition, I would like to know if there is another formula that could be added in order to list the concatenated values from lowest to highest.
Example (random data):
ID: Year:
1 2010
1 2008
1 2009
Based on this data, I would like to have the years listed as follows: 2008,2009,2010. However, without doing any trasnformations to your DAX code, I get the numbers this way: 2010,2008,2009.
Hope I have explained myself clearly enough that you are able to help me with this.
Thanks,
Oscar F.
Hello,
I am trying to use this DAX formula but instead of getting the concatenated branche values specific to each ID, I get all branche values for each ID in my table. Any idea what I am doing wrong?
Thanks.
Nevermind, I found the problem 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |