Solved: Re: Combine values of multiple rows in one row

TheoM · ‎11-22-2017

Hi all,

I am stuck with a challenge in Power Query, which should not be very difficult to solve, but i haven't found the solution yet.

I have a table that links activities to persons. An activity can be linked to one or more persons, a person can be linked to one ore more activities. Every row contains one activity and one person, so if an activity has more than one person, there will be more rows for that activity.

I want to transform the table to a table that hase unique activity ID's and a column that contains all persons linked to that activity.

I have summarized the table on Activity ID (see below), but how do I get the multiple values into one column of the table?

summarized by activity IDResult should look like this for selected row

MarcelBeug · ‎11-22-2017

You can group on ACTIVITYID, choose some dummy operation for FULLNAME (e.g. Max, not "All Rows") and then adjust the generated code to combine the names:

let
    Source = #table(type table[ACTIVITYID = number, FULLNAME = text],{{1, "Pietje Puk"},{2, "Maarten de Ruijter"},{2, "Sjaak van den Hoek"}}),
    #"Grouped Rows" = Table.Group(Source, {"ACTIVITYID"}, {{"Persons", each Text.Combine([FULLNAME], ", "), type text}})
in
    #"Grouped Rows"

Specializing in Power Query Formula Language (M)

View solution in original post

bschirm · ‎04-11-2025

Hi, I have the following set of data (only the first several rows are shown). I want to combine rows for every change in eDAS Code column. So, the first 3 rows would be combined, the 4th row remains the same, the 5th, 6th, 7th rows are combined, etc. I someone able to help me figure out how to accomplish this in Power Query?

Ashish_Mathur · ‎04-11-2025

Hi,

Share some data to work with and show the expected result very clearly. Share data in a format that can be pasted in an MS Excel file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

bschirm · ‎04-14-2025

Original:

Index	Service Id	eDAS Code	Quantity	Rate	Amount Due	Description	End Date	Start Date	Project Name	Project Number	Resource	Customer	Date	Project	Task	Application	COMPCODE	ELAPSED	INITDATE	INITTIME	JOB$ADJ	JOBNAME	JOBNUM	Name, First	Name, Last	SID	SYSPROC	TERMDATE	TERMTIME	USERID	Application ID	First Name	Last Name	System ID	Transaction ID	User ID	Connecting	Server Name	URL	Data Set Name	Date Created	Days Billed	Size (GB)	Volume	EndDate	GB Hours	StartDate	Location	Server	TSM Backup Space
1	4219	0019	1	139.54	139.54	Insight Inv 1101248267
2							3/10/2026
3								3/11/2025
4	4219	E2GG	1	290.94	290.94	Insight Inv 1101248674
5	4219	427S	1	674.39	674.39	Insight Inv 1101242246
6							1/26/2026
7								1/27/2025
8	4219	427Z	1	674.39	674.39	Insight Inv 1101242246
9							1/26/2026
10								1/27/2025
11	4219	427S	1	674.39	674.39	Insight Inv 1101242246
12							1/26/2026
13								1/27/2025
14	4219	E22M	1	38230.5	38230.5	Insight Inv 1101255381
15							3/10/2026
16								3/11/2025
17	4219	0019	1	139.54	139.54	Insight Inv 1101256955
18							3/10/2026
19								3/11/2025
20	4219	0019	1	25182.18	25182.18	Carahsoft Inv IN1836910

Ashish_Mathur · ‎04-16-2025

Based on the data that you have shared, show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

bschirm · ‎04-17-2025

Ok, I thought that I had uploaded that also. Here is a screenshot:

Here is the sample data: Note that there are blank columns, additional data on rows uploaded are not in the sample data.

Index

Service Id

eDAS Code

Quantity

Rate

Amount Due

Description

End Date

Start Date

Project Name

Project Number

Resource

Customer

Date

Project

Task

Application

COMPCODE

ELAPSED

INITDATE

INITTIME

JOB$ADJ

JOBNAME

JOBNUM

Name, First

Name, Last

SID

SYSPROC

TERMDATE

TERMTIME

USERID

Application ID

First Name

Last Name

System ID

Transaction ID

User ID

Connecting

Server Name

URL

Data Set Name

Date Created

Days Billed

Size (GB)

Volume

EndDate

GB Hours

StartDate

Location

Server

TSM Backup Space

1

4219

0019

1

139.54

Insight Inv 1101248267

3/10/2026

3/11/2025

2

4219

E2GG

1

290.94

Insight Inv 1101248674

3

4219

427S

1

674.39

Insight Inv 1101242246

1/26/2026

1/27/2025

4

4219

427Z

1

674.39

Insight Inv 1101242246

1/26/2026

1/27/2025

5

4219

427S

1

674.39

Insight Inv 1101242246

1/26/2026

1/27/2025

6

4219

E22M

1

38230.5

Insight Inv 1101255381

3/10/2026

3/11/2025

7

4219

0019

1

139.54

Insight Inv 1101256955

3/10/2026

3/11/2025

8

4219

0019

1

25182.18

Carahsoft Inv IN1836910

9

4219

309C

1

349.99

SSL Cert Renewal

Ashish_Mathur · ‎04-17-2025

The data does not get pasted properly in an Excel sheet. Please share the download link of an MS Excel file with 2 tabs - input and output.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

bschirm · ‎04-18-2025

I'm not sure if this works, but will give it a try:

https://docs.google.com/spreadsheets/d/169-JkLrSaUxjP9qutEcfjwSEixHAb35-/edit?usp=drive_link&ouid=10...

Ashish_Mathur · ‎04-18-2025

Hi,

This M cdoe seems to work

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Service Id", Int64.Type}, {"eDAS Code", type text}, {"Quantity", Int64.Type}, {"Rate", type number}, {"Amount Due", type number}, {"Description", type text}, {"End Date", type text}, {"Start Date", type text}, {"Project Name", type any}, {"Project Number", type any}, {"Resource", type any}, {"Customer", type any}, {"Date", type any}, {"Project", type any}, {"Task", type any}, {"Application", type any}, {"COMPCODE", type any}, {"ELAPSED", type any}, {"INITDATE", type any}, {"INITTIME", type any}, {"JOB$ADJ", type any}, {"JOBNAME", type any}, {"JOBNUM", type any}, {"Name, First", type any}, {"Name, Last", type any}, {"SID", type any}, {"SYSPROC", type any}, {"TERMDATE", type any}, {"TERMTIME", type any}, {"USERID", type any}, {"Application ID", type any}, {"First Name", type any}, {"Last Name", type any}, {"System ID", type any}, {"Transaction ID", type any}, {"User ID", type any}, {"Connecting", type any}, {"Server Name", type any}, {"URL", type any}, {"Data Set Name", type any}, {"Date Created", type any}, {"Days Billed", type any}, {"Size (GB)", type any}, {"Volume", type any}, {"EndDate", type any}, {"GB Hours", type any}, {"StartDate", type any}, {"Location", type any}, {"Server", type any}, {"TSM Backup Space", type any}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"eDAS Code"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"eDAS Code"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"eDAS Code"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Index")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"eDAS Code", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Grouped Rows1" = Table.Group(#"Merged Columns", {"Merged"}, {{"Count", each Table.AddIndexColumn(_,"Index1",1)}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows1", "Count", {"Value", "Index", "Index1"}, {"Value", "Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.2]), "Merged.2", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"})
in
    #"Removed Columns1"

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

bschirm · ‎04-21-2025

Thank you. I'll give it a try this week. Appreciate your assistance.

pbinewbieee · ‎10-05-2023

any idea on how to combine 3 rows of comments? having a hard time combining 3 comments under comment_field column. need to combine 3 rows of column by latest time and date.

Ashish_Mathur · ‎10-06-2023

Hi,

Share some data in a format that can be pasted in an MS Excel file, explain the question and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

samkamal230 · ‎06-12-2023

Hi!

I am facing a problem similar to this. But my scenario differs as follows:

I have 3 columns, having the following data (there are more columns, but these are the ones that have unique values):

I want it to be converted to the following:

I want to do this transformation in Power Query, but am unable to do it completely (not able to achieve it completely). How can I achieve this? (Note: I don't want duplicate values to be concatenated in a single row when it comes to the columns "category" and "sub-category". There should be a unique value in columns "id" and "name" and the other columns should have distinct values concatenated.

Thanks in advance.

Ashish_Mathur · ‎06-12-2023

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1vX391TSAbKcHBWS84Esl9SkEpCAs7OjUqwOdjVAFODr6RuATwGKAQYGRiAhHx9HP4WckhSENcGOfvhUOecXFRA2C4QI2hTgi+RerGZA3BILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t, category = _t, #"sub-category" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"category", "sub-category"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"id", "name"}, {{"Categories", each Text.Combine(List.Distinct([category]),", "), type nullable text}, {"Sub categories", each Text.Combine(List.Distinct([#"sub-category"]),", "), type nullable text}})
in
    #"Grouped Rows"

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

samkamal230 · ‎06-13-2023

Hi @Ashish_Mathur I tried it out, by tweaking the source to fit my case. It worked perfectly! Thank you so much once again!!!

Ashish_Mathur · ‎06-13-2023

You are welcome. If my previous reply helped, please mark that reply as Answer.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

samkamal230 · ‎06-13-2023

Thanks @Ashish_Mathur for your response. I don't understand the M code aparently. Can you please elaborate? I have created a merged table from various table and have 14 columns in that table (4 of which I have mentioned in my example because they have unique value). So do I need to make changes in the M code regarding the columns I want to include? I particularly don't understand the source step (as i am fetching data from dynamics 365 crm into tables and then merging those tables into a single table on which I want to achieve the desired functionality):

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1vX391TSAbKcHBWS84Esl9SkEpCAs7OjUqwOdjVAFODr6RuATwGKAQYGRiAhHx9HP4WckhSENcGOfvhUOecXFRA2C4QI2hTgi+RerGZA3BILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t, category = _t, #"sub-category" = _t])

Can you please elaborate further? Thanks again for your input. 🙌

Theo1403 · ‎06-12-2023

Hi,

The suggested solution in this thread should work for you scenario as well, the only difference is that you should group by two columns and add a dummy calculation for the columns you want to combine. Then edit the query by replacing the dummy calculation by the Text.Combine function.

I didn't have the chance to test this yet because I'm not near a computer atm but let me know if this works otherwise I would like to give it a try in power query

Regards, Theo

Anonymous · ‎04-25-2020

Anyone know how to handle this? I want attachments averaged and Limit added together for the rows with the identical company name.

		Limit	Attachment	Broker
	XYZ Company	$ 25,000,000	$ 100,000,000	XYZ Broker
	XYZ Company	$ 10,000,000	$ 250,000,000	XYZ Broker
	X Company	$ 15,000,000	$ 25,000,000	X Broker
	Y Company	$ 12,000,000	$ 50,000,000	XYZF Broker


Desired Result
	XYZ Company	$ 35,000,000	$ 175,000,000	XYZ Broker
	X Company	$ 15,000,000	$ 25,000,000	X Broker
	Y Company	$ 12,000,000	$ 50,000,000	XYZF Broker

Ashish_Mathur · ‎04-25-2020

Hi,

Drag Company and Broker to the row labels and write this measure

Limits = SUM(Data[Limit])

Attachments = AVERAGE(Data[Attachmnet])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Brian_M · ‎11-22-2017

Hi,

I would expand the table to new rows so that each user is on its own row with the corresponding ActivityID and then Table.Group function to combine the names grouped by ActivityID

Ignore the first few rows setting up an example table.

let
    Source = #table({"ActivityID","Users"}, {{ 1234,#table({"Users"},{{{"Alice Wonderland", "Bob Talon-Ted", "Jim McCustard"}}}) }, {1235,#table({"Users"},{{{"Billy Snob-Thornton", "Sally Harry","Willian E Bob Thornton"}}})}}),
    #"Expanded Users Table" = Table.ExpandListColumn(Source, "Users"),
    #"Expanded Users List Record" = Table.ExpandRecordColumn(#"Expanded Users Table", "Users", {"Users"}, {"UserList"}),
    #"Expanded Users List to Rows" = Table.ExpandListColumn(#"Expanded Users List Record", "UserList"),

    //Group all the user rows by ActivityID using the Text.Combine function
    #"Group Users by ActivityID" = Table.Group(#"Expanded Users List to Rows", {"ActivityID"}, {{"Persons", each Text.Combine([UserList],", "), type text}})
in
    #"Group Users by ActivityID"

Hope that helps!

Combine values of multiple rows in one row

Helpful resources

Power BI Monthly Update - April 2025

NEW! Community Notebooks Gallery

Fabric Community Update - April 2025

How to Get Your Question Answered Quickly

Enter to win a FREE Fabric certification exam voucher

Combine values of multiple rows in one row

Helpful resources

Power BI Monthly Update - April 2025

NEW! Community Notebooks Gallery

Fabric Community Update - April 2025

How to Get Your Question Answered Quickly