Learn how to generate lists in Power BI from scratch

Generating lists in Power BI from scratch

Learn how to generate lists in Power BI from scratch

Disconnected lists of items can be useful for various purposes. For example, one can be used as a parameter table.

In Power BI, you can generate lists from scratch with both M and DAX. We’ll start with generating a continuous list of integers.

The M way

  1. Start with Get Data → Blank Query
    Click Get Data, then Blank Query
  2. Make sure you have the Formula Bar displayed
    Click the View tab in the ribbon, then check Formula Bar
  3. To generate a list of integers from –5 to 5, inclusive, type = {-5..5} in the formula bar
    Type the above code in the formula bar
  4. You then need to convert the list to table
    Use the special Transform tab in the ribbon to transform the list into table
  5. Simply click OK in the dialog
    Click OK in the new dialog
  6. Continue styling as needed (rename the query and column, assign the data type) and click Close & Apply. In the end, you get a continuous list
    A continuous list of integers from –5 to 5, inclusive

The DAX way

You can also generate lists with pure DAX, thanks to the CALENDAR function:

  1. Click Modeling → New Table
    Select the Modeling tab of the ribbon and click New Table
  2. For a list of integers from –5 to 5, inclusive, type the following code in the formula bar:
    DAX Integers List = SELECTCOLUMNS ( CALENDAR ( -55 )"Integers"INT ( [Date] ) )

    DAX Integers List = SELECTCOLUMNS ( CALENDAR ( -5, 5 ), "Integers", INT ( [Date] ) )

You get the same list of integers as with M:

A continuous list of integers from –5 to 5, inclusive

With DAX, you don’t need to open the Query Editor to create a list, and you define the table and column names and data type all in one statement, so this approach is quicker.

Other types of lists

Using either of the approaches, you can also generate lists of characters and decimals, as well as non-continuous lists.

Non-continuous lists

In M, typing {-1..0, 0, 2} gives you a list that consists of –1, 0, 0 and 2:

The list consists of four items: –1, 0, 0 and 2

To achieve the same result in DAX, you should use the following code:

DAX Non-Continuous List =
SELECTCOLUMNS (
    UNION ( CALENDAR ( -10 )CALENDAR ( 00 )CALENDAR ( 22 ) ),
    "Integers"INT ( [Date] )
)

Lists of characters

For characters, you rely on the fact that each character has a Unicode number. For example, the capital letter A’s number is 65. To get the full Latin alphabet in capitals, you use the following DAX formula:

DAX Letter List =
SELECTCOLUMNS (
    CALENDAR ( 126 ),
    "Letters"UNICHAR ( 64 + INT ( [Date] ) )
)

With Power Query M, you simply type = {"A".."Z"} to achieve the same result.

List of letters from A to Z

Lists of decimals

To generate a list of decimals, you need to generate a list of integers first and then divide them, like so:

DAX Decimals List = SELECTCOLUMNS ( CALENDAR ( 010 )"Decimals"INT ( [Date] ) / 10 )

You get a list of numbers from 0 to 1 with 0.1 interval:

List of numbers from 0 to 1 with a step of 0.1

If you feel queryious, try creating the same list with M in Query Editor 🙂

The following two tabs change content below.

Daniil Maslyuk

Daniil Maslyuk, MCPS, is a business intelligence consultant with background in finance and sales in transnational corporations. Daniil is particularly passionate about Microsoft Power BI and its family of products.

Latest posts by Daniil Maslyuk (see all)