cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Create A Number Table

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

Highlighted
Microsoft
Microsoft

Re: Create A Number Table

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

Highlighted
Community Champion
Community Champion

Re: Create A Number Table

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!

Connect on Twitter
Connect on LinkedIn

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: Create A Number Table

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

Highlighted
Helper III
Helper III

Re: Create A Number Table

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

Highlighted
Microsoft
Microsoft

Re: Create A Number Table

Hi @moizsherwani,

 

Are you using Excel or Power BI Desktop?


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

Proud to be a Datanaut!

Highlighted
Helper III
Helper III

Re: Create A Number Table

PowerBI Desktop

Highlighted
Microsoft
Microsoft

Re: Create A Number Table

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

Highlighted
Helper III
Helper III

Re: Create A Number Table

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"

Highlighted
Community Champion
Community Champion

Re: Create A Number Table

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!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Create A Number Table

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!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors