cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonMeadows
Resolver I
Resolver I

Patch or remove the current item in a ForAll loop

Hi,

 

I have a collection of time ranges that I am trying to intersect with anoter set of time ranges.

I am using a ForAll to loop through one set and check if they intersect with the other set.

If they overlap I am creating new time ranges for the intersection but I want to remove the original time range or mark it to be ignored in subesequent calculations so that my resulting list of time ranges is contigouos with no overlapping.

 

How do I make a change to the current record being iterated through in a ForAll function?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @SimonMeadows

No need for the ForAll, in fact.  I try to avoid it as performance is not very good usually (see here if interested).

Here is what you need for the ranges:

ClearCollect(RangesAll, RangesA.StartA, RenameColumns(RangesA.EndA, "EndA", "StartA"), RangesB.StartA, RenameColumns(RangesB.EndA, "EndA", "StartA"));
ClearCollect(RangesDistinct, Sort(Distinct(RangesAll, StartA), Result, Ascending) );
ClearCollect(RangesMerged, AddColumns(RenameColumns(FirstN(RangesDistinct,CountRows(RangesDistinct)-1),"Result","StartA"), "EndA", First(Filter(RangesDistinct, Result>StartA)).Result))

I will have a think about the text labels when I have a little more time.  I can send you the sample app if you send me your email via private message. Screenshot below.

 

__.JPG

View solution in original post

7 REPLIES 7
Meneghino
Community Champion
Community Champion

Hi @SimonMeadows

I would create (i.e. Clear and then Collect into with the ForAll loop) a third (temporary) collection to store the result.

Then after the ForAll loop has run, just ClearCollect the temporary collection into your current collection.

Please note that the ForAll loop in theory has parallel execution, so that the order of looping through the items is not guranteed to be the same as the order of the items.  You should consider whether this may affect the result.

I hope this helps.

 

Clear(TemporaryCollection);
ForAll(CurrentCollection, Collect(TemporaryCollection, My looping logic etc.); Other action etc.);
ClearCollect(CurrentCollection, TemporaryCollection)

Hi @Meneghino

 

That makes sense, I have tried a temporary collection but I am using a nested ForAll for the second set of time ranges.

This results in many aditional records being added to the temp collection for each set of time ranges that does not meet the overlap condition.

 

It's porbably best I explain i a bit more detail what I'm trying to do

If I have the following collections

 

RangesA
{
StartA: 1
EndA: 4
OtherInfo: "Thing1"
}
{
StartA: 4
EndA: 9
OtherInfo: "Thing2"
}
{
StartA: 9
EndA: 16
OtherInfo: "Thing3"
}


RangesB
{
StartA: 5
EndA: 11
OtherInfo: "ThingZ"
}

The result I am needing is

 

RangesResult
{
StartA: 1
EndA: 4
OtherInfo: "Thing1"
}
{
StartA: 4
EndA: 5
OtherInfo: "Thing2a"
}
{
StartA: 5
EndA: 9
OtherInfo: "Thing2Z"
}
{
StartA: 9
EndA: 11
OtherInfo: "Thing3Z"
}
{
StartA: 11
EndA: 16
OtherInfo: "Thing3a"
}

As you can see I have split [Thing2] and [Thing3] into [Thing2a, Thing2Z, Thing3Z, Thing3a]

To keep the overall time range contiguous I need to remove [Thing2] and [Thing3]

Is there a way to do this inside a nested ForAll?

 

Hi @SimonMeadows

The issue is clear, thank you.

What is the logic of the OtherInfo column?  And how should the two text values be combined? (i.e. the one from the first collection, and the one from the second) In other words why '2a' or '3a' in the result?

PS I don't think you need a nested ForAll, but it may depend on your answers to the above questions.

Hi @Meneghino,

 

Apologies, my naming was for disambiguation form the original records to the new records.

 

Ideally im looking for the non overlapped areas of the records to keep ther original values and the overlappped to take the values of the RangesB Record.

 

So really they should be [Thing2, ThingZ, ThingZ, Thing3]

Hi @SimonMeadows

No need for the ForAll, in fact.  I try to avoid it as performance is not very good usually (see here if interested).

Here is what you need for the ranges:

ClearCollect(RangesAll, RangesA.StartA, RenameColumns(RangesA.EndA, "EndA", "StartA"), RangesB.StartA, RenameColumns(RangesB.EndA, "EndA", "StartA"));
ClearCollect(RangesDistinct, Sort(Distinct(RangesAll, StartA), Result, Ascending) );
ClearCollect(RangesMerged, AddColumns(RenameColumns(FirstN(RangesDistinct,CountRows(RangesDistinct)-1),"Result","StartA"), "EndA", First(Filter(RangesDistinct, Result>StartA)).Result))

I will have a think about the text labels when I have a little more time.  I can send you the sample app if you send me your email via private message. Screenshot below.

 

__.JPG

View solution in original post

@Meneghino When you say looping logic in your ForAll example, what does that actually look like?

 

It is a good question, probably some sort of shaping expression that creates one or more rows as needed.  Could you be more specific about why you need this?  As you see from the solution, ForAll can be avoided (and should be when possible).

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,785)