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

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's avatar
GerryL
Frequent Visitor
4 years ago

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

 

 

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