Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

How to Extract a portion of a single-column collection?

Posted on by

Help please. Below is a portion of a single-column, 50-rows collection shown in a gallery.

How could I create another collection that consists of only the 8 rows (yellow) beginning with the line “Address:” and including or excluding blank rows? Put another way, I want to extract the 8 rows (yellow) of collection1 to a new collection.

I’ve tried this (among others) but it does not work as I want. It returns only the first row and not the following 7 rows.

OnSelect: ClearCollect(colAddress, FirstN( Search(colByNewLine, "Address:","Result"),8))

Gallery showing collection1Gallery showing collection1

Thank you for any help.

Chris

 

Categories:
  • Verified answer
    ChrisChong Profile Picture
    ChrisChong on at
    Re: How to Extract a portion of a single-column collection?

    Thanks to your genius, you got me 98% of the way there. And your response was excellent.

    I thank you.

    Chris

  • ChrisChong Profile Picture
    ChrisChong on at
    Re: How to Extract a portion of a single-column collection?

    Pure genius. Thank you. I admit I thought of a similar solution but I convinced myself there must be another way. I am pretty sure your solution will work. Please allow me to implement it and report back.

    Thanks again.

    Chris

  • Verified answer
    mdevaney Profile Picture
    mdevaney 30,012 on at
    Re: How to Extract a portion of a single-column collection?

    @ChrisChong 

    If the text does not always appear in the same place then is is necessary to add a new column for Row Number to your collection.  When you detect the position of "Address:" you can get the Row Number and then supply it to the LASTN + FIRSTN formula I shared.

     

    //Row counter
    ClearCollect(rowCounter,{Value:1});
    
    //Create a new collection with the row number
    ForAll(
     colByNewLine,
     Collect(myNumberedCollection,{RowNumber: First(rowCounter).Value, Result: colByNewLine[@Result]});
     Patch(
     rowCounter,
     First(rowCounter),
     {Value: First(Sort(myNumberedCollection,RowNumber,Descending)).RowNumber + 1}
     );
    );
    
    //Find what row Address appears on
    Set(startingRow, Lookup(myNumberedCollection, Result = "Address:", RowNumber);
    
    

     

    Then you could supply this code to the Items property of your gallery to make the Address section appear

    LastN(FirstN(myNumberedCollection, startingRow + 7), 7)

     

     ---
    Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

  • ChrisChong Profile Picture
    ChrisChong on at
    Re: How to Extract a portion of a single-column collection?

    No, I could not use the code you suggest: LastN(FirstN(colByNewLine, 16), 7) because the desired portion does not consistently begin on the same row as in this example. That's the reason I wanted to extract 8 rows beginning with the row containing the word, "Address:"

    I appreciate your suggestions.

    Chris

  • mdevaney Profile Picture
    mdevaney 30,012 on at
    Re: How to Extract a portion of a single-column collection?

    @ChrisChong 

    That screenshot is VERY helpful.  Could you isolate the desired information using LASTN and FIRSTN  functions?

     

    LastN(FirstN(colByNewLine, 16), 7)

     

    ---
    Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up." 

  • ChrisChong Profile Picture
    ChrisChong on at
    Re: How to Extract a portion of a single-column collection?

    Ok. I appreciate very much your help.

    GallerySample4.pngGallerySample3.png

    Chris

  • mdevaney Profile Picture
    mdevaney 30,012 on at
    Re: How to Extract a portion of a single-column collection?

    @ChrisChong 

    Can I see more of what Collection1 looks like in the Collections Viewer.  I am still trying to wrap my head around the structure of that collection.

  • ChrisChong Profile Picture
    ChrisChong on at
    Re: How to Extract a portion of a single-column collection?

    So sorry to be misleading. The yellow highlighting was made by cutting/pasting from PA Studio into a in a graphics program in an attempt to make what I was referring to clearer. As to the code,

    • I have a single-column collection I called "collection1" of some 55 rows; the single column name is "Result".
    • I would like to create a 2nd collection--let's call it "collection2"-- which should be a subset of collection1.
    • Collection2 should contain only 8 rows of collection1 beginning with the row containing the word, "Address:" 

    Hope this is clearer.

    Chris

  • mdevaney Profile Picture
    mdevaney 30,012 on at
    Re: How to Extract a portion of a single-column collection?

    @ChrisChong 

    That was only a test to see what the result of SEARCH was and not meant to be a fix.

     

    Can you please give further details (including code) on how you made the text highlighted in yellow?

     

    ---
    Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

     

  • ChrisChong Profile Picture
    ChrisChong on at
    Re: How to Extract a portion of a single-column collection?

    I'm sorry to report that I still get only the row showing "Address:". Here are the screen shots if it helps:
    GallerySample2.png

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,487

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 64,014

Leaderboard