Split multiperson column into rows

(0) Share

Report

Posted on by douglasfilipe

Hi all

I have this SharePoint list

ID	SurveyID	ThemeID	Order	Question	Responsible
109	2	1	3	pergunta teste	Johnson, Bruce Field, Sally
108	2	2	2	Teste pergunta composto	Nyugen, Sam Johnson, Bruce
94	10	6	68	A avaliação do resultado dos treinamentos é verificada por meio de:	Field, Sally

Loaded in my canvas app named ColQuestions, now I need to convert column Responsible in rows, like this:

ID	SurveyID	ThemeID	Order	Question	Responsible
109	2	1	3	pergunta teste	Johnson, Bruce
109	2	1	3	pergunta teste	Field, Sally
108	2	2	2	Teste pergunta composto	Nyugen, Sam
108	2	2	2	Teste pergunta composto	Johnson, Bruce
94	10	6	68	A avaliação do resultado dos treinamentos é verificada por meio de:	Field, Sally

How can I get this result?

Categories:

Using Apps Using Formulas

All responses (3)

Answers (0)

Nandit 1,563 Super User 2025 Season 1 on at

Like (0)

Report

Split multiperson column into rows

I just noticed you mentioned its a Multiperson column, please disregard my suggestion and use WarrenBelz's suggestion, it will work perfectly!
Suggested answer

Nandit 1,563 Super User 2025 Season 1 on at

Like (0)

Report
Split multiperson column into rows

Hi douglasfilipe,

You will need a delimiter like ";" between the names in the Responsible column.

Quick question what type of column is Responsible in your SharePoint list?

If you have a Delimiter ";", you can use the following code to generate the output you require -

I created the dummy dataset as a collection:

ClearCollect( colQuestions, { ID: 109, SurveyID: 2, ThemeID: 1, Order: 1, Question: "Test", Responsible: "Johnson, Bruce; Field, Sally" }, { ID: 108, SurveyID: 2, ThemeID: 1, Order: 1, Question: "Test2", Responsible: "Nyugen, Sam; Johnson, Bruce" } )

Then I run the following code to create the required output:

ForAll( colQuestions As Record, ForAll( Split( Record.Responsible, ";" ) As Person, Collect( colQuestions_Converted, { ID: Record.ID, SurveyID: Record.SurveyID, ThemeID: Record.ThemeID, Order: Record.Order, Question: Record.Question, Responsible: Person.Value } ) ) )

Here's how it looked:

Hope this helps.

Kind regards,

Nandit

If this answers your query, please mark this response as the answer.
If its helpful, please leave a like. Thanks!
WarrenBelz 146,645 Most Valuable Professional on at

Like (1)

Report
Split multiperson column into rows

Hi douglasfilipe

Use the Ungroup function
Ungroup( SPList, Responsible )

Note that the amount of records able to be addressed is restricted to your Data Row Limit.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee