Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |