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}.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂