Create a table with values between from and to columns

[Devon_Runner]

HI

I would like to create a new table that shows the numbers in each Group ID, between 2 Ref ID columns

Current Data table

Group ID	Ref ID from	Ref ID to
Test1	500	999
Test2	1000	1499

Requirement

Group ID	Ref ID
Test1	500
Test1	501
Test1	502
Test1	999
Test2	1000
Test2	1001
Test2	1002
Test2	1499

[Anonymous · Accepted solution]

Hi, @Devon_Runner 

I used your information to create a table named Table:

Then try the following DAX expression to create a new table:

NewTable = 
SELECTCOLUMNS(
    ADDCOLUMNS(
        GENERATE(
            'Table',
            GENERATESERIES('Table'[Ref ID from], 'Table'[Ref ID to], 1)
        ),
        "Ref ID", [Value]
    ),
    "Group ID", 'Table'[Group ID],
    "Ref ID", [Value]
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

In Power Query {500..999} is a list that contains all the numbers between these 2 limits.

So,  add a custom column and use the column headings to make your list, then expand the list.