Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moizsherwani
Continued Contributor
Continued Contributor

Create A Number Table

Hello everyone,

 

So since there is not a way to get a userinput box I am reverting to creating a table of numbers and having the user select via a slicer (they need to select their salary) and I have not been able to find a quick way to generate a table with numbers (similar to how we generate one for creating a date table). Can anyone help? So again I am just looking for a table like the one below.

 

TABLE

10,000

10,001

10,002

10,003

10,004

.....

1,000,000

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.
3 ACCEPTED SOLUTIONS
Phil_Seamark
Employee
Employee

Hi @moizsherwani,

 

Try a blank query in the Query Editor and paste this in to the advanced editor

 

let
    Source = List.Generate(()=>10000, each _ < 1000000, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Did you try pasting this into the Advanced Editor?

 

let
    Source = List.Generate(()=>10000, each _ < 1000000, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

list.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

OwenAuger
Super User
Super User

I don't recommend creating a DAX calculated table for this, but if you wanted to for fun you could use this:

NumberTable =
VAR MinNumber = 10000
VAR MaxNumber = 1000000
RETURN
    SELECTCOLUMNS (
        CALENDAR ( MinNumber, MaxNumber ),
        "Number", INT ( [Date] )
    )

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

8 REPLIES 8
OwenAuger
Super User
Super User

I don't recommend creating a DAX calculated table for this, but if you wanted to for fun you could use this:

NumberTable =
VAR MinNumber = 10000
VAR MaxNumber = 1000000
RETURN
    SELECTCOLUMNS (
        CALENDAR ( MinNumber, MaxNumber ),
        "Number", INT ( [Date] )
    )

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
moizsherwani
Continued Contributor
Continued Contributor

The only way I have figured out so far is to do a Power Query -> New Query -> {10000...1000000}

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Hi @moizsherwani,

 

Are you using Excel or Power BI Desktop?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

PowerBI Desktop

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Did you try pasting this into the Advanced Editor?

 

let
    Source = List.Generate(()=>10000, each _ < 1000000, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

list.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes, your solution works perfectly, I am more a DAX fan so was wondering if there was a way to do this in Dax, otherwise your solution is the "BALL"

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.

Try this for something a bit different in DAX

 

New Table = SELECTCOLUMNS(CALENDAR(DATE(1927,5,18),DATE(4637,11,26)),"n",int([Date]))

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark
Employee
Employee

Hi @moizsherwani,

 

Try a blank query in the Query Editor and paste this in to the advanced editor

 

let
    Source = List.Generate(()=>10000, each _ < 1000000, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.