Results 1 to 10 of 10

Thread: Position of the first number in a list lower than n

  1. #1
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,849
    Articles
    0
    Excel Version
    365

    Position of the first number in a list lower than n



    Register for a FREE account, and/
    or Log in to avoid these ads!

    If I have:
    Code:
    let
        Source = {7,7,6,4,7,3,4,4,5,1},
        Posn = List.PositionOf(Source,4)
    in
        Posn
    I get 3.
    What I really want is (air code)
    Code:
        Posn = List.PositionOf(Source,<6)
    that is the position of first occurence of a value less than 6 (the return being 3).
    How?

    Ancillary question: Can I do it from the end of the list instead of from the beginning, ie. the last number less than 6 in the list?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    This should do it

    Code:
         Posn = List.PositionOf(Source, List.FirstN(List.Select(Source, each _ < 6), 1){0})
    If you want from the end, reverse the list, List.Reverse

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,849
    Articles
    0
    Excel Version
    365
    That'll do nicely, thank you!

    Quote Originally Posted by Bob Phillips View Post
    This should do it

  4. #4
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    119
    Articles
    0
    Excel Version
    Excel 365
    There is a function List.PositionOfAny. See the picture below.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,849
    Articles
    0
    Excel Version
    365
    Prompted to play around further,
    Code:
    Posn=List.PositionOfAny(List.Transform(Source,each _ < 6),{true},0)
    copes with possibilities of negative numbers and decimal numbers and means no arithmetic needed to find the last position in the original list having found the position in a reversed list.
    Is it heavier on resources, or slow, or bad practice?

    I had looked at PositionOfAny but mistakenly thought the 3rd argument was the 1st, 2nd, 3rd, 4th occurence, not an Occurrence.Type of first, last and all.
    Where can I get something like intellisense as in Bill's picture? When writing formulae in a spreadsheet cell you get prompted:
    Click image for larger version. 

Name:	2020-10-15_122143.png 
Views:	6 
Size:	14.2 KB 
ID:	10096
    as well as in the VBE.
    Last edited by p45cal; 2020-10-15 at 02:49 PM.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by Bill Szysz View Post
    There is a function List.PositionOfAny. See the picture below.
    Bill, your picture is missing. I accidentally deleted this post (I was deleting the posts of some idiot who posted the same post to every forum), and although I undeleted it I think that caused the image to go. Could you be so good a to re-post it?

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by p45cal View Post
    Prompted to play around further,
    Code:
    Posn=List.PositionOfAny(List.Transform(Source,each _ < 6),{true},0)
    copes with possibilities of negative numbers and decimal numbers and means no arithmetic needed to find the last position in the original list having found the position in a reversed list.
    A better way in my original suggestion would be to use List.LastN rather than reverse it as I suggested (although I also agree Bill's suggestion of List.PositionOfAny is better).
    Last edited by Bob Phillips; 2020-10-15 at 02:26 PM.

  8. #8
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    119
    Articles
    0
    Excel Version
    Excel 365
    Quote Originally Posted by Bob Phillips View Post
    Could you be so good a to re-post it?
    As you wish, Bob :-))
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Explanation.jpg 
Views:	18 
Size:	37.0 KB 
ID:	10095  

  9. #9
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    119
    Articles
    0
    Excel Version
    Excel 365
    Quote Originally Posted by p45cal View Post
    Where can I get something like intellisense as in Bill's picture?
    This was done in excel (manually)
    but....look at this post https://community.powerbi.com/t5/Com...ad/ba-p/146347
    At the end.... there are many ways to do what you need... i strongly recommend trying each one before choosing (performance)
    At the end of the end... one more piece of code to do the same
    Code:
    = List.Count(List.FirstN(Source, each _ >= 6))

  10. #10
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    Thanks for that Bill.

    Your 'intellisense' is a lot better than the documentation, which describes the occurrence argument as

    occurrence: The maximum number of occurrences that can be returned.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •