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}})


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:

 {"First Column", "Second Column"}, 


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():

 type table
        #"Number Column"=number, 
        #"Text Column"=text,
        #"Date Column"=date


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.

50 responses

  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:

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

    Similar, yet a bit different. 🙂

    • Yes. You could get exactly the same table by just using this expression:

      #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})

      No let expression needed, and a lot less code.

    • Hi Kenpuls,

      This is awesome stuff and precisely what i need. I like this statement “I also source that from a table created from no data source, but generally create it like this:”

  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. Pingback: Dew Drop – June 6, 2016 (#2266) | Morning Dew

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


    • 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.

  5. Or what if the URL responds with:

    type table
    #”Number Column”=number,
    #”Text Column”=text,
    #”Date Column”=date

    And then you write in M:


    • 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?

      • Creating an OData feed involves implementing metadata semantics. LOTS of code. PITA. This would be much easier. Power Query M doesn’t have generic way to pull structured data besides OData, so your post made me think #table() could be an elegant solution.

  6. 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().

  7. 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.

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

    • 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.

      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}})

      • 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. 🙂

  8. Pingback: Creating Sequences Of Integers And Characters In Power BI/Power Query Lists – Chris Webb's BI Blog

  9. 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.

  10. Pingback: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI – Chris Webb's BI Blog

  11. Pingback: Generating JSON In Power BI And Power Query – Chris Webb's BI Blog

  12. Pingback: Setting Data Types On Nested Tables In M – Chris Webb's BI Blog

  13. Hi Chris,

    Is there a way I can generate a calendar table function with monthly increment rather than day. I only want to have month, quarter and year columns in it.
    I have created a calendar table with consecutive dates, removed date column, then removed duplicate entries. While I achieved the structure I wanted, is there a better, cleaner way of doing it?

    • Yes, there will be a cleaner way of doing this with some relatively simple M code. However if you have already got something working I’m not sure it’s worth the effort to write the new code if you’re not comfortable with M.

  14. Pingback: More Details On Creating Tables In Power BI/Power Query M Code Using #table() « Chris Webb's BI Blog

  15. Is there a way to insert a row permanently using M, like
    table1 = table1 + raw1,
    table1 = table1 + raw2….

    Many Thanks

    • To do that you need to use this code in a query – the name of the query becomes the name of the table, and after you have loaded the output of the query to Power BI or Excel you can use it in a report.

  16. Pingback: A Function To Generate The M Code For A Table Type « Chris Webb's BI Blog

  17. If I need to save the data generated by my charts and store it in table, can i use mquery to store historical data- so that upon refresh the historical table will keep growing when the model is refreshed daily?

    • Yes, this is what the incremental refresh feature in either a published dataset or a dataflow to do this. Both require Power BI Premium though. There are some other workarounds using Excel but they aren’t reliable and I would not recommend them.

  18. Pingback: Fourteenth Blog Birthday « Chris Webb's BI Blog

  19. Pingback: DST Refresh Date Function Power BI Service-daylight-savings-time

  20. Pingback: Adding More Aggregate Columns To The Output Of Table.Profile « Chris Webb's BI Blog

  21. Hi Chris,
    I was annoyed by all the replace values I have in a table. I was wondering if it could be possible to (for a specific column) create a virtual table with the original text and the replace text and then do a List.ReplaceMatchingItems and return the replace text instead of the original value in the column.

    I have not been able to find any solution for that – no matter how I search – and was hoping for your creativity

    • Did you know you can use functions in the replace arguments?

      #”Replaced Value” = Table.ReplaceValue(#”Changed Type”,”7/11/2019″,each if [ApplicationID] = 34 or [ApplicationID] =35 or [ApplicationID] =36 then “Baseline” else [Period],Replacer.ReplaceText,{“Period”})​

  22. Pingback: Chris Webb's BI Blog: A Function To Generate The M Code For A Table Type Chris Webb's BI Blog

  23. Pingback: Chris Webb's BI Blog: Generating JSON In Power BI And Power Query Chris Webb's BI Blog

  24. How can i refer a tablecolumn type? For exemple, i got a query with a column where the type should be the same type from the First column from other query when i define the tablet type.

  25. Pingback: Springwater 04 | Revenue and cost types | neatdot

  26. Hi Chris,
    Just wondering how to use M to set a variable with the Table Name, and then use it in DAX?

  27. Pingback: Chris Webb's BI Blog: Adding Tooltips To Column Headers In The Power Query Editor In Power BI And Excel Chris Webb's BI Blog

  28. Hi Chris, this was very useful to understand the basic application of m query. is there any way to create date table in power query. thanks and looking forward to hear from you.

  29. Hello, Everyone!
    I have the following doubt:
    I have a set of data. I am adding a visualization to my report using the “matrix” option. I have realized that this matrix would be important to further explore my data.
    How can I create a table that resembles the matrix created automatically for the visualization?
    The rows show unique product codes, columns show unique Names of Clients, values are showing a distinct count of product code. So the matrix is basically showing if a given client ordered a specific product. The matrix either shows empty cells or 1 .
    Thanks for your attention!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: