Today we are going to discuss about numerous methods in Power Query to generate sequence of numbers from given grouped numbers. See below picture to get clear idea about the requirement.
Method-1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lt = List.Transform,
Result = List.Combine( Lt(Source[Problem], (f) =>
try
[
Lst = Lt(Text.Split(f,"-"), Number.From) ,
seq = {Lst{0}..Lst{1}}
]
[seq]
otherwise
{Number.From(f)}))
in
Result
We can use field access operator, i.e. [ ] to extract a column from table and convert this into list structure as shown below,
=Source[Problem]
List. Transform function will help us to transform our list,
(f) => is the variable replacement of custom function which is generally used as each _.
From now on, f refers current item in list.
Inside our function in List. Transform we used 2 variables Lst and seq,
Lst refers a list, in which each element contains a list which we call as List within List.
First element of our outer list is '1-2',
if we split this by delimiter"-", then this single item will become list of {1,2}
To convert each item of our inner list into number we can use Number. From function.
So, Lst will look like below,
seq variable helps us to create sequence of numbers from lower value of inner list to upper value of inner list.
So, if our f is 13-16, then
Lst will become {13,16}
seq will become {13,14,15,16}.
From these 2 variables, we extract seq which is our desired result using square brackets.
In our outer list, when we have values without interval such as 9 and 21, we can't split this into delimiter "-", so Lst will throw an error here.
To overcome this error, we wrap our function with try and otherwise construction,
wherever we get error, this construction will provide the same value of 9 and 21.
List. Combine function will help us to combine and each list and to deliver us the final list.
Method-2:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Nf = Number.From,
Result = List.Combine(List.Transform( Source[Problem], (f) =>
if Text.Contains(f,"-")
then
[
Lst =Text.Split(f,"-"),
Gen = List.Generate(
() => [i = Nf(Lst{0}), n = 0],
each [n] < Nf(Lst{1}) - Nf(Lst{0}) + 1,
each [i = [i] + 1, n = [n] + 1],
each [i])
]
[Gen]
else {Nf(f)}))
in
Result
In this method, we generate the sequence of numbers using List. Generate function.
First argument in this function is Initial,
So, we declared initial values of 2 variables,
consider the element of 13-16 in our outer list,
we split this using delimiter "-", then this will become {13,16}.
[i] -> first element in our list {13,16} which is 13,
[n] -> counter for iteration, we put 0 as initial value.
2nd argument is condition,
here we declare our condition for the loop,
upper value of list is 16,
lower value of list is 13,
So, 16-13+1 which is 4, is our iteration count.
We ask the function to do the iteration till [n] is less than 4.
3rd argument is next,
Here, we add 1 to variable [i] in each iteration and we add 1 to variable [n] in each iteration.
4th argument is select,
Here, we select our desired result which is variable [i].
in this method to overcome the error, we used if and Text. Contains function construction.
If text contains "-" then do List. generate construction for inner list else deliver the same item in outer list.
Comments