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:
#table(
{"First Column", "Second Column"},
{
{1,"Hello"},
{#date(2016,1,1),3}
}
)
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)}
}
)
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.
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
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. 🙂
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.
Awesome tips for creating the table “Last Refresh”, thank you.
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:”
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.
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.
That’s why I added the Expression.Evaluate()
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?
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.
I would also be worried about using Expression.Evaluate() because, in theory, a hacker could inject any code they liked into your query and cause all kinds of problems! Maybe a more realistic objection is that if you read Curt Hagenlocher’s comments on this post https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/ you’ll see that using Expression.Evaluate() could result in performance problems.
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().
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. 🙂
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.
There are lots of intrinsic functions. You need to download the Power BI Language spec, which is available here: https://msdn.microsoft.com/en-us/library/mt211003.aspx
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.
Is there a way to insert a row permanently using M, like
table1 = table1 + raw1,
table1 = table1 + raw2….
Many Thanks
No, because values in M are immutable. What you can do is create a new table with the new rows in (I guess what your raw1 and raw2 represent), and use Table.Combine (https://msdn.microsoft.com/en-us/query-bi/m/table-combine) to combine the data from the original table with the new table.
Thanks for a quick reply Chris, even if the answer is not what I was hoping for.
To clarify, I was hoping there is a way of using M to insert raw permanently to an existing table, but obviously its not the case.
Thanks again
Mariusz
You’re right, it isn’t possible in M. There are some hacks to do this (eg this old post of mine: https://blog.crossjoin.co.uk/2013/05/13/accumulating-data-in-an-excel-table-using-data-explorer-and-powerpivot/) but they are very hacky. The only reliable way of doing this is with Power BI’s incremental refresh functionality which for now is only available with Power BI Premium but which will, eventually, be in Power BI Pro too.
Well, it’s nice to be able to make a table. But how can I give it a proper name to then use its fields in a report?
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.
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.
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”})
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.
Hi Chris,
Just wondering how to use M to set a variable with the Table Name, and then use it in DAX?
Cheers,
R
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.
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?
Example:
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!