Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I need it somewhere in the future

Sometimes you need a numbers table (also called tally table) when you’re working with data. It’s a table with one single integer column that contains incremental numbers (0,1,2,3,4,…). In a database, it’s fairly easy to generate using either cross-joins or a generator function (GENERATE_SERIES in T-SQL). Recently, I needed such a table in Power BI Desktop. Suppose you don’t have a database as a source (or maybe you cannot change the database), so we need to generate this in Power Query. First, add a blank query:

Then you go to the advanced editor, where you paste the following code:

let
    Source =  List.Generate(()=>0, each _ <= 100, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "NumberCol"}})
in
    #"Renamed Columns"

This will generate a table with 101 rows, starting at 0 and ending with 100.

UPDATE:

I got this reaction from Alex Powers – a Fabric aficionado from Microsoft – on Bluesky:

This means we can replace the first line of the M code with the elegantly simple {0..100}.

The post Create a Numbers Table in Power Query first appeared on Under the kover of business intelligence.