List.Numbers Within a Range

[GerryL]

Hello,

I'm trying to generate a list of numbers in my dataset within a range from 10-50 in an increment of 10. In other words, as it generates the number for each row, when it gets to 50, it should go back to 10. I can't figure out a way to do this with List.Numbers. 

[Vijay_A_Verma]

Let's say you want to generate 18 numbers...Use below formula (replace 18 with the required number as per your need)

= List.Generate(()=>[x=10,i=1], each [i]<=18, each [i=[i]+1, x=if [x]=50 then 10 else [x]+10], each [x])

[GerryL]

Thanks. This isn't working. It says a Token Comma is expected.

[Vijay_A_Verma]

You can't replace () with anything else. Everything else is correct. It has to be

=List.Generate(()=>[x=10...........

[GerryL]

I got it to generate numbers, but not in the way that I need. Here's a sample of my data and what I am trying to do. This is already after much transformation to get to this point, and this is the last bit I need to create a reference table.

My data is currently set up like this:

Merged Program Coding	Simplified Program Status	From	To	To Year	To Term	From Year	From Term	Years
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2012
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2013
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2014
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2015
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2016
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2017
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2018
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2019
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2020
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2021
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2022
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	2023
MS_CS_ONWAY:C620:CECS	Active	202320	202320	2023	20	2023	20	2023
CERT_CHS:H518:HCPS	Active	202320	202320	2023	20	2023	20	2023
MS_BUS:B435:BMKT	Active	202320	202320	2023	20	2023	20	2023
MS_BUS:B434:BFIE	Active	202320	202320	2023	20	2023	20	2023
BS_PHD_CS:C620:CECS	Active	202320	202320	2023	20	2023	20	2023
MS_BUS:B517:BMGT	Active	202120	202320	2023	20	2021	20	2021
MS_BUS:B517:BMGT	Active	202120	202320	2023	20	2021	20	2022
MS_BUS:B517:BMGT	Active	202120	202320	2023	20	2021	20	2023

I used List.Numbers to generate the years column using the From Year and To Year and now I need to do the same for From Term and To Term. The expected result is one row per term, so for example it would be something like this (once I combine the year and term again)

Merged Program Coding	Simplified Program Status	From	To	To Year	To Term	From Year	From Term	Term Codes
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201250
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201310
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201320
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201430
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201440
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201450
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201510
MS_NSG:H502:HNUR:H522	Active	201250	202320	2023	20	2012	50	201520

[Vijay_A_Verma]

So, do you need to generate 50,10,20,30,40,50,10,20,30,40,50.....

[GerryL]

Yes. Based on what would be there for the range between the From and To columns. I had to break the year (first 4 digits in From and To columns) from the term (last 2 digits) do handle them separately with the plan being to put it back together. Years are easy, but the logic for the term is what is getting me stuck.

[Vijay_A_Verma]

I notice that for 2012, you have generated 50.

For 2013 - 10, 20

For 2014 - 30, 40, 50

What is the logic behind this?

(These numbers I am reading from last column which is named as Term Codes)

[GerryL]

Hi,

201250 is the first one, based on the fact that it exists in the From for that program.  So 50 is the starting point and is broken out into the From Term column.  For each year on that same program, I have to generate the terms (from 10 to 50) starting with the first one (in the example it starts with 50 in the year 2012. So it would go 201250, 201310, 201320. . . 201410, 201420, etc. until it gets to 2022320 (the To column).