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