cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GerryL
Frequent Visitor

List.Numbers Within a Range

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. 

 

 

8 REPLIES 8
Vijay_A_Verma
Super User
Super User

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])

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

 

GerryL_0-1654267252715.png

 

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

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

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 CodingSimplified Program StatusFromToTo YearTo TermFrom YearFrom TermYears
MS_NSG:H502:HNUR:H522Active2012502023202023202012502012
MS_NSG:H502:HNUR:H522Active2012502023202023202012502013
MS_NSG:H502:HNUR:H522Active2012502023202023202012502014
MS_NSG:H502:HNUR:H522Active2012502023202023202012502015
MS_NSG:H502:HNUR:H522Active2012502023202023202012502016
MS_NSG:H502:HNUR:H522Active2012502023202023202012502017
MS_NSG:H502:HNUR:H522Active2012502023202023202012502018
MS_NSG:H502:HNUR:H522Active2012502023202023202012502019
MS_NSG:H502:HNUR:H522Active2012502023202023202012502020
MS_NSG:H502:HNUR:H522Active2012502023202023202012502021
MS_NSG:H502:HNUR:H522Active2012502023202023202012502022
MS_NSG:H502:HNUR:H522Active2012502023202023202012502023
MS_CS_ONWAY:C620:CECSActive2023202023202023202023202023
CERT_CHS:H518:HCPSActive2023202023202023202023202023
MS_BUS:B435:BMKTActive2023202023202023202023202023
MS_BUS:B434:BFIEActive2023202023202023202023202023
BS_PHD_CS:C620:CECSActive2023202023202023202023202023
MS_BUS:B517:BMGTActive2021202023202023202021202021
MS_BUS:B517:BMGTActive2021202023202023202021202022
MS_BUS:B517:BMGTActive2021202023202023202021202023

 

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 CodingSimplified Program StatusFromToTo YearTo TermFrom YearFrom TermTerm Codes
MS_NSG:H502:HNUR:H522Active201250202320202320201250201250
MS_NSG:H502:HNUR:H522Active201250202320202320201250201310
MS_NSG:H502:HNUR:H522Active201250202320202320201250201320
MS_NSG:H502:HNUR:H522Active201250202320202320201250201430
MS_NSG:H502:HNUR:H522Active201250202320202320201250201440
MS_NSG:H502:HNUR:H522Active201250202320202320201250201450
MS_NSG:H502:HNUR:H522Active201250202320202320201250201510
MS_NSG:H502:HNUR:H522Active201250202320202320201250201520

 

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

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.

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)

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).

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors