Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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.
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.
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.
User | Count |
---|---|
143 | |
71 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |