Combine values of multiple rows in one row

[TheoM]

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

[pbinewbieee]

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]

Hi,

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

[samkamal230]

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]

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.

[samkamal230]

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]

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

[samkamal230]

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]

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]

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]

Hi,

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

Limits = SUM(Data[Limit])

Attachments = AVERAGE(Data[Attachmnet])

Hope this helps.

[Brian_M]

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!

[Brian_M]

The previous reply beat me to it!!

[MarcelBeug · Accepted solution]

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"

[SonaSinghA]

Thank you

[TheoM]

Thanks, this does the job!

[Anonymous]

Can you please explain How you did this? I have same requirement where I have same month in different rows and want to combine data of same month in one row. I am still in learning phase of Power BI so a detailed explanation will be very helpful.

Thanks:)

[TheoM]

Hi,

You need to group on "Month" and then use each Text.Combine statement like the example earlier in this thread. Text.Combine can not be chosen in the user interface, so you need to enter the code in the advanced query editor OR choose on of the options you can choose from the user interface (like minimum or maximum) and change the code in the advanced editor.

If this doesnt help, send me an example (or screenshot) so i can see how your table looks like. In that case i can try to create the code for you.

Have fun!

[Anonymous]

So here is the screen shot of my Table. I understand when we need to select Column and then click on Grouping. Do I need to enable any settings while writing this query?

As you see in screen shot I need to have my Planned month grouping done and have a column with rows on different Month and summarize the Initial FC value in one row for each month. I know I am asking very basic question and hopefully If i get a detailed steps from you, I will be more comfortable in handling the queries moving forward.. Thanks for being my Tutor.

[TheoM]

Hi,

I understand your goal is to summarize the FC column by month. This can be done in PowerQuery, but I would prefer to create a measure in DAX, because a measure would provide a more dynamic way to get summarized data based on filter context. If you do decide to do it in Power Query, you should take the next steps:

Select Date Column

Hit Group By, select Basic and enter a name for the summary column (for example FC Month)

Operation: choose Sum

Column: choose FC (the column for which you want to sum the values)

It should look like this:

Hit OK and your output looks like this:

When you take a look at the advanced query editor, the code you created looks like this:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Planned Date"}, {{"FC Month", each List.Sum([Initial FC]), type number}})

I hope this is helpful

Theo

[Anonymous]

Hi Theo,

Yes it was helpful but it seems I am not going to leave you soon.:) ..When I tried to do the same I am able to create a new Table alltogether.

Now, I have other fields as well where I would like to do the grouping for examplesee in my below table, I have Planned Date and Actual Date column, I would like to have Planned Date grouping done by Initial FC and Actual Date grouping done by YTD. I want this to be in a Same table so that I can plot a graph with Month and YTD and FC.

Hope I am now clear on what I am trying to achieve and you will be able to guide me in right direction. I am there but not sure how to keep it in one table everything. Thanks for being helpful.