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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

21 REPLIES 21

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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