Forum Discussion

GerryL's avatar
GerryL
Frequent Visitor
4 years ago

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 shou...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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

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

GerryL's avatar
GerryL
Frequent Visitor
4 years ago

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

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

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

    • GerryL's avatar
      GerryL
      Frequent Visitor
      4 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        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)