CODEWITHSUNDEEP
excelpowerbipowerquerym
David542
David542

Reputation: 110217

Range function in M?

Is it possible to create a numerical range in M? For example something like:

let
    x = range(1,10) // {1,2,3,4,5,6,7,8,9,10}, from 1 to 10, increment by 1
    x = range(1,10,2) // {1,3,5,7,9}, from 1 to 10, increment by 2

Upvotes: 0

Views: 86

Answers (1)

chillin
chillin

Reputation: 4486

For simple scenarios, a..b might be appropriate. Some examples:

let
    firstList = {1..10}, // {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
    secondList = {1, 5, 12, 14..17, 18..20}, // {1, 5, 12, 14, 15, 16, 17, 18, 19, 20}
    thirdList = {Character.ToNumber("a")..100, 110..112}, // {97, 98, 99, 100, 110, 111, 112}
    fourthList = {95..(10 * 10)} // {95, 96, 97, 98, 99, 100}
in
    fourthList

Otherwise, maybe try a custom function which internally uses List.Generate:

let
    range = (inclusiveStart as number, inclusiveEnd as number, optional step as number) as list => 
        let
            interval = if step = null then 1 else step,
            conditionFunc = 
                if (interval > 0) then each _ <= inclusiveEnd
                else if (interval < 0) then each _ >= inclusiveEnd
                else each false,
            generated = List.Generate(() => inclusiveStart, conditionFunc, each _ + interval)
        in generated,
    firstList = range(1, 10), // {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
    secondList = range(1, 10, 2), // {1, 3, 5, 7, 9}
    thirdList = range(1, 10 , -1), // {} due to the combination of negative "step", but "inclusiveEnd" > "inclusiveStart"
    fourthList = range(10, 1, 0), // {} current behaviour is to return an empty list if 0 is passed as "step" argument.
    fifthList = range(10, 1, -1), // {10, 9, 8, 7, 6, 5, 4, 3, 2, 1}
    sixthList = range(10, 1, 1) // {} due to the combination of positive "step", but "inclusiveEnd" < "inclusiveStart"
in
    sixthList

The above range function should be able to generate both ascending and descending sequences (see examples in code).

Alternatively, you could create a custom function which uses List.Numbers internally by first calculating what the count argument needs to be. But I chose to go with List.Generate.

Upvotes: 2

Related Questions