none
Convert listed numbers from a single cell in Excel worksheet to PQ List RRS feed

  • General discussion

  • What's the best way to convert entry from the single cell in Excel, e.g. 1,3,5..9,11,13..15 into the fully functional list in PQ, like {1,3,4,6,7,8,9,11,13,14,15}?

    My initial guess was be to use List.Generate but not sure how to implement it...

    I loaded that entry from cell into PQ, did a split by comma, added columns with start and end numbers and then tried to use List.Generate and that didn't work.

    Turned out I had to use List.Numbers to create the list for numbers separated by double dot. After that it's trivial.

    May be there is a way to do it with List.Generate but so fat List.Numbers is working all right.


    • Changed type Yury Suturin Monday, April 6, 2020 12:01 AM Found an answer
    • Edited by Yury Suturin Monday, April 6, 2020 12:03 AM
    Sunday, April 5, 2020 9:30 PM

All replies

  • Hi Yury

    #1 "1,3,5..9,11,13..15" should give {1,3,5,6,7,8,9,11,13,14,15}, not {1,3,4,6,7,8,9,11,13,14,15}

    #2 List.Numbers function is dedicated to generate Numbers while the powerful List.Generate isn't. So, logically, I would expect the 1st to do a better job from a performance perspective - hope this makes sense. However, on a small set as the above one the diff. is necessarily imperceptible

    #3 My initial guess was be to use List.Generate but not sure how to implement it...
    If you did it with List.Numbers this means you identified the 1st number (13 in i.e. 13..15). So taking the same approach, identifying the 15 shouldn't be a problem I guess. With this, just adapt List.Generate Example 1 and you're done :): List.Generate(()=> 13, each _ <= 15, each _ +1)

    My approach to this would be:

    let
        //
        fxGenNumList = (inText as text) as list =>
            let
                Split = Text.Split(inText,".."),
                FirstNum = Number.From(List.First(Split)),
                LastNum = Number.From(List.Last(Split)),
                ListNumbers = List.Numbers(
                    FirstNum, (LastNum - FirstNum)+1
                ),
                ListGenerate = List.Generate(()=> FirstNum,
                    each _ <= LastNum,
                    each _ +1
                )
            in
                ListNumbers,   /* OR: ListGenerate */
        //
    
        Source = "1,3,5..9,11,13..15",
        Split = Text.Split(Source,","),
        Transform = List.Transform(Split, each
            if Text.Contains(_,"..")
            then fxGenNumList(_) else {Number.From(_)}
        ),
        Combine = List.Combine(Transform)
    in
        Combine

    Note:

    • I kept the List.Generate option - for you - while I would use List.Numbers for reasons in #2
    • Ideally fxGenNumList function should be outsourced (not in query as above)

    Hope this all makes sense & helps. Nice day...

    Monday, April 6, 2020 8:42 AM