Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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.
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 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 |
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).
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
15 | |
12 |