Dax, combine, List all values into one new column based on an existing column

[gracie494]

Hello, how do I create a new column that combines all values in one column into One.

(See example Below) The goal is to create the new column based on every value in year

RU	Year	New Column
REDS	2020	2018, 2019, 2020
LOWS	2019	2018, 2019, 2020
DEDS	2020	2018, 2019, 2020
HAPPS	2018	2018, 2019, 2020

[SanketBhagwat]

Hi @gracie494 .

Please refer this link ;

https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/

If this post helps, then please mark it as 'Accept as Solution'.

Regards,

Sanket Bhagwat

[SanketBhagwat · Accepted solution]

@gracie494 Did that help?

If not, you can achieve that by adding a new column.

Create a new calculated column as ;

New Column=CONCATENATEX(Table,Table[YEAR],",")

Regards,

Sanket Bhagwat

[TCLC]

I want to do something similar, but I need to only combine emails into a combined email columns specific to a company. Any ideas? I'll be using this in a dashboard to select companies I want to communicate with and copy-paste the value of the combined emails into an email.  I was able to this in excel using another table with TEXTJOIN. =TEXTJOIN("; ",TRUE,(P9:P251))

Note this is a huge contact list, & the companies I communicate with have different scope of work, so this needs to filterable in the dashobard by the scope of work as well as the company. 

Example: 

Company:	Contact Name:	Email:	Combined
ABC Electric	Jane Doe	j.doe@ABCelectric.com	j.doe@ABCelectric.com; j.smith@ABCelectric.com; t.allen@ABCelectric.com
ABC Electric	John Smith	j.smith@ABCelectric.com	j.doe@ABCelectric.com; j.smith@ABCelectric.com; t.allen@ABCelectric.com
ABC Electric	Tim Allen	t.allen@ABCelectric.com	j.doe@ABCelectric.com; j.smith@ABCelectric.com; t.allen@ABCelectric.com
Another Co	Tom Hastings	Tom@anotherco.net	Tom@anotherco.net; Jen@anotherco.net; Rob@anotherco.net
Another Co	Jen Archer	Jen@anotherco.net	Tom@anotherco.net; Jen@anotherco.net; Rob@anotherco.net
Another Co	Robert Lang	Rob@anotherco.net	Tom@anotherco.net; Jen@anotherco.net; Rob@anotherco.net

[gracie494]

Thanks so much, I had to modify the fomula to 

Listed Year = CONCATENATEX ( DISTINCT(RU[Financial Year]),RU[Financial Year],",") and now it works.

Kind Regards 🙂