CODEWITHSUNDEEP
excelpowerpivotdaxpowerquery
Alex
Alex

Reputation: 947

How do you return a a set of matching rows and concatenate one of the columns?

I want to do something like this:

=related(othertable[aColumn])

But where there are multiple matching rows it should concatenate all of the aColumn values that match.

Something like this, but working:

=concatenate(values(filter(othertable,othertable[bColumn]=[value in this table])))

Upvotes: 0

Views: 2949

Answers (2)

Jeff
Jeff

Reputation: 12785

Jason Thomas answered this question on his blog post Group Multiple Rows to Single Delimited Row in PowerPivot

In that post he explains that if you first create a parent/child hierarchy then you can use PATH to return a comma separated list.

He uses the RANKX function and a little bit of logic to create the parent/child relationship, then calculates the parent value for the column of interest, and finally uses the PATH function to calculate the comma seperated list.

Upvotes: 1

Alex
Alex

Reputation: 947

I couldn't figure out how to do this in DAX so instead did it in Power Query.

//using this as a start:
aTable = #table({"A","B","C"},{{1,"a",1},{1,"b",2},{2,"c",3},{3,"d",4}}),
//I'm concatenating text...so need to have a function stating what to concatenate with
fCombine = Combiner.CombineTextByDelimiter(":"),
aGroupRowsCat = Table.Group( 
  aTable, 
  {"A"}, 
  {{"CatOfB", each fCombine([C]), type text}} 
)

I end up with:

A|CatOfB
-+------
1|a:b
2|c
3|d

Upvotes: 1

Related Questions