Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
TheoM
Helper I
Helper I

Combine values of multiple rows in one row

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 IDsummarized by activity IDResult should look like this for selected rowResult should look like this for selected row

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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

30 REPLIES 30
Brian_M
Responsive Resident
Responsive Resident

The previous reply beat me to it!!

MarcelBeug
Community Champion
Community Champion

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)

Thank you

Thanks, this does the job!

Anonymous
Not applicable

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:)

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
Not applicable

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.


Testdata.JPG

 

 

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:

screenshot 1.png

Hit OK and your output looks like this:

screenshot 2.png

 

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
Not applicable

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.

 

Capture.JPG

Hi
You should not do calculation work in queries, make those in dax. If you add a date table to your model, you will be able to calculate YTD totals up to specific dates and apply filters which makes your data model much more dynamic. That’s the power of power bi. Take a look at the videos in the Guided Learning chapter of the power bi website. They were very helpful for me

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.