web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps Split a row in a colle...

Power Apps

Answered

Split a row in a collection into multiple rows based on value in column

(0) Share

Report

Report

Posted on by Community Power Pla... Microsoft Employee

Hi everyone,

I have the following situation:

We have a SQL-database that contains timesheet entries. The columns are the following:

assignment_id , entry_date , update_date , hours , comment

In PowerApps I create a collection that looks like this:

The comment is build up like this:

[#102;1,5;qqqqqq]

#102: This is the user story number (not to be confused with ID) - 1357 is the corresponding ID of this number

1,5: Number of hours

qqqqqqq: description

Now here comes the hard part. It can occur that the comment contains multiple user stories like:

[#102;1,5;qqqqqq][2;3;Test]

I would like to split the collection if this occurs. Desired outcome:

UserStoryId , UserStoryName , assignment_id , comment , entry_date , hours, update_date

1357 Task 1 72646 [#102;1,5;qqqqqq] 2/2/2023 1,5 2/2/2023

1358 Task 2 72646 [2;3;Test] 2/2/2023 3 2/2/2023

How can one achieve this?

Categories:

Building Power Apps

All responses Img

All responses (2)

Answers Img

Answers (1)

Sort by

Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous,

Okay, the sign of the splitting thing is the nothing, right?

If so, I think you'd better add a delimiter between each [], like a ^ or other delimiters differs from ";" and comma ",".

Or you could firstly replace all the "][" with "],[", here is how I achieve this.

1). Create a collection to replace all the "][" with "],[":

ClearCollect(coooo,ForAll(YourCollection,Substitute(Comment,"][","],[")))

2). Then generate another collection as below:

Clear(Collection2);ForAll(coooo, ForAll(Split(Value,"^"),Collect(Collection2,{Comment:Result})));
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi v-qiaqi-msft,

I found a workaround, but I got another request to not only accept "][" but also "}{", "}[" or "]{" and that is where I am stuck, since I want to use the solution you proposed earlier to add the delimiter like ^ between them.

When I try this, it only gives me the result of the comment and not the rest of the attributes I need. Perhaps I am doing something wrong.
Clear(Collection2);ForAll(coooo, ForAll(Split(Value,"^"),Collect(Collection2,{Comment:Result})));

This is my formula now:

Clear(colTimesheetEntries); ForAll( varTimesheetEntries As _entry, ForAll( Split( _entry.comment, "][" ) As _comment, With( { varValues: Split( Substitute( Substitute( _comment.Result, "[", "" ), "]", "" ), ";" ) }, Collect( colTimesheetEntries, { Date: _entry.entry_date, Project: _entry.Project, Hours: Coalesce( Last( FirstN( varValues, 2 ) ).Result, _entry.hours ), Comment: Coalesce( Last(varValues).Result, _comment.Result ) } ) ) ) );

Join the conversation