Creating Tables In Power BI/Power Query M Code Using #table()

After my post earlier this week on creating current day/week/month/year reports in Power BI a few people asked me for a more detailed explanation of the way I was creating tables without using a data source in my M code. This is something I find myself doing quite a lot when I’m loading data with Power BI and Power Query, and while there are several ways of doing this I find that using the #table() intrinsic function is the most elegant option.

Let’s look at some examples. The following query returns a table with two columns (called “First Column” and “Second Column”) and two rows containing the values from 1 to 4:

#table({"First Column", "Second Column"}, {{1,2},{3,4}})

image

No data source is needed – this is a way of defining a table value in pure M code. The first parameter of the function takes a list of column names as text values; the second parameter is a list of lists, where each list in the list contains the values on each row in the table.

In the last example the columns in the table were of the data type Any (the ABC123 icon in each column header tells you this), which means that they can contain values of any data type including numbers, text, dates or even other tables. Here’s an example of this:

#table(
 {"First Column", "Second Column"}, 
 {
  {1,"Hello"},
  {#date(2016,1,1),3}
 }
)

image

While this is flexible it’s not exactly practical: in almost all cases the Any data type is a bad choice for loading data, and you need to explicitly set the data type for each column. You can set data types for columns quite easily as a separate step, but it is also possible to set column data types using #table():

#table(
 type table
    [
        #"Number Column"=number, 
        #"Text Column"=text,
        #"Date Column"=date
    ], 
 {
  {1,"Hello",#date(2016,1,1)},
  {2,"World",#date(2017,12,12)}  
 }
)

image

In this example the first parameter is no longer a list of column names but a declaration of a table type that not only has column names in but also column types. You can see from the icons in the column headers in the screenshot above that the column called “Number Column” has a data type of number, “Text Column” has a data type of text, and “Date Column” has a data type of date.

Of course if you need a fixed table value in Power BI you could use the “Enter Data” button or, if you’re using Excel and Power Query you could create an Excel table and then use the Excel.CurrentWorkbook() function to load the contents of it; if you or your end users need to edit the values in your table easily then you should use one of these two options. On the other hand if you don’t want users to be able to edit the values in the table or, more likely, you are generating the contents of your table using functions that return lists (as in my previous post) then #table() is the way to go.

24 thoughts on “Creating Tables In Power BI/Power Query M Code Using #table()

  1. One of my favourite tricks for dashboards is to include a “last refresh” date so we can see a) if the refresh is actually working and b) how stale the data is. I also source that from a table created from no data source, but generally create it like this:

    let
    Source = Table.FromRecords({[LastRefresh=DateTime.LocalNow()]}),
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“LastRefresh”, type datetime}})
    in
    #”Changed Type”

    Similar, yet a bit different. 🙂

  2. Hi Chris, do you see any chance to use this or another concept to store historical data directly inside the Power BI model? All my approaches so far failed because I found no way to keep data of previous runs…

    • No, there is no reliable way you can store historical data in Power BI. You have to use an external SSAS model or use Direct Query on a relational database if you want to do it.

  3. Hi Chris,
    Would this be a way to pull a typed data table from a URL that responds with contents in the #table format?

    #table(
    Expression.Evaluate(
    Web.Contents(“https://somedomain.com/data”)
    )
    )

    • No, I don’t think that would work – #table() takes two parameters, and this example only has one so it would error. I don’t even think it’s possible for a Web.Contents() to return anything that can be recognised as a table on its own.

  4. Or what if the URL responds with:

    #table(
    type table
    [
    #”Number Column”=number,
    #”Text Column”=text,
    #”Date Column”=date
    ],
    {
    {1,”Hello”,#date(2016,1,1)},
    {2,”World”,#date(2017,12,12)}
    }
    )

    And then you write in M:

    Expression.Evaluate(
    Web.Contents(“https://somedomain.com/data”)
    )

    • Ah, ok, I understand. Yes, I guess that would work although I think you’d need to have some extra code to treat what Web.Contents() returns as text. Why would you want to do that though? Wouldn’t it just be easier to, say, create an OData data source that returned the same table?

  5. Two valid points indeed. Really just wish that Power Query had a way to pull structured data. Good old Excel Web Queries (IQY) could do this, but I see no way in Power Query. What I’ve been doing is to have an M generator. It generates M that includes setting the column data types for JSON returned from Web.Contents().

  6. Hi Chris
    In your previous post, you provided the following code for the calendar table. It needs a starting date and a number of dates. I wonder if we could do the same thing using the ending date instead of the number of dates.
    Actually, your code uses 750 days and then you were missing the last day of 2016 since there is a 29-Feb-2016, namely 2016 has 366 days.
    Of course you could replace 750 by 751, but It is not that elegant.

    let
    DateList = List.Dates(#date(2015,1,1), 730, #duration(1,0,0,0)),
    AddColumns = List.Transform(
    DateList,
    each
    {_, Date.Month(_), Date.Year(_)}
    ),
    DateTable = #table(type table[Date=date, Month=number, Year=number], AddColumns)
    in
    DateTable

    • I’ve personally never been a fan of using List.Dates for this for the leap year reason. My preference is to create a list of values, then convert them to a table and transform them into dates. Because you specify the start/end, leap years are auto-magically included where appropriate.

      let
      Source = {Number.From(#date(2015,1,1))..Number.From(#date(2020,12,31))},
      ConvertToTable = Table.TransformColumnTypes(Table.FromList(Source, Splitter.SplitByNothing(),{“DateKey”}, null, ExtraValues.Error),{{“DateKey”, type date}})
      in
      ConvertToTable

      • To be honest, that code was something I put together quickly to get some dates into a table – I wasn’t expecting anyone to look at it too closely, sorry. In fact when I really want to create a date table I use something more like the CreatePeriodTable function in that post that does take a start and an end date, finds the number of days between them using Duration.Days() and then uses that instead of a hard-coded number of days.

      • LOL! Well, I’d never use hard coded dates either. I usually read them from an Excel table. It was more about the method. But then again, the method you showed above was something I did leverage for my last post on displaying the last updated date/time. So different methods for different purposes. 🙂

  7. Hi Chris, thanks for your post. Just wondering, are there any other “intrinsic functions” such as #table, and where are they documented? It looks like there are #date, #time, and #duration as well, but these are just hinted at in the documentation on MSDN.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s