The Text.Format() Function In Power BI/Power Query M

New functions are appearing in M all the time, many with no announcement or documentation. I came across Text.Format() the other day and I thought it was worth blogging about because I can see a lot of uses for it: it makes it very easy to insert values into a piece of text.

The function signature is:
Text.Format(formatString as text, arguments as any, optional culture as nullable text)

Here’s a simple example:

Text.Format(“The #[Animal] sat on the #[Furniture]”, [Animal=”cat”, Furniture=”mat”])

It returns the text:

The cat sat on the mat


As you can see, the references to each record field in the first piece of text are replaced with the values from those fields from the record in the second parameter. Those of you who know a little M will realise how this works: the placeholder in the text passed to the first parameter is actually the same M expression you would use to extract the value you need from the record in code. So [Animal] is the M expression you’d use to return the value from the Animal field from the record [Animal=”cat”, Furniture=”mat”], as in following expression which returns the text value “cat”:

    MyRecord = [Animal="cat", Furniture="mat"],
    GetAnimal = MyRecord[Animal]

The second parameter can take other data types too. You can pass a list instead of a record; so for example the expression


“The first number is #{0}, the second number is #{1}, the third number is #{2}”,


returns the text

The first number is 5, the second number is 8, the third number is 9

The optional third parameter of Text.Format() controls the locale/culture used when formatting the values. So for example the expression


“Decimal example #{0} – Date example #{1}”, {100.001, #date(2015,12,1)},


returns a decimal number and date formatted for US English, with a full stop (or period, as the Americans say) as the decimal separator and the date shown as mm/dd/yyyy:

Decimal example 100.001 – Date example 12/1/2015

While the expression


“Decimal example #{0} – Date example #{1}”, {100.001, #date(2015,12,1)},


Returns the same values formatted for French/France, where the decimal separator is a comma and dates are formatted dd/mm/yyy:

Decimal example 100,001 – Date example 01/12/2015

How about some more advanced examples? Here’s a table in Excel:


If you load it into Power Query and then create a custom column, in your custom column expression you can refer to the current row as a record using the _ (underscore) character. So creating a custom column using the following expression:

Text.Format(“the #[Animal] sat on the #[Furniture]”, _)


Returns a table that looks like this:


You could also use Text.Format() to create parameterised queries to run against a database. Here’s an example of an MDX query on the Adventure Works DW database with a parameterised WHERE clause:

    MDXQueryTemplate = 
	{[Measures].[Internet Sales Amount]} ON 0,#(lf)
	[Date].[Calendar Year].[Calendar Year].MEMBERS ON 1#(lf)
	[Adventure Works]#(lf)
    ReplaceCountryParameter = 
    RunQuery = 
		"adventure works dw 2008", 

Remember, if you do something like this you’ll probably want to disable native database prompts – if you don’t, you’ll be asked to approve every new query that gets run. Also, you’ll notice that I’m using the StrToMember() function with the Constrained flag in the WHERE clause because, even though it’s not really necessary, it’s good from a security point of view. It would be really good if we could use proper MDX parameters in our queries but I don’t think it’s possible, unless there’s some other new feature or function that I don’t know about.

10 responses

  1. Pingback: Dew Drop – December 3, 2015 (#2144) | Morning Dew

  2. @Chris
    To concatenate we could just use & –
    Source = Excel.CurrentWorkbook(){[Name=”D”]}[Content],
    mAddCol = Table.AddColumn(Source, “Custom”, each “The ” & [Animal] & ” sat on the ” & [Furniture])

  3. Thanks Chris- Exactly what I was looking for – I wish in the Advanced Editor they introduce intellisense so that functions become more discoverable

  4. Pingback: The Text.Format() Function In Power BI/Power Query M |

  5. Hi Chris,
    Please tell me if you can think of a simpler way to do the following?
    Given the following table, extract the names into a new column using the values in the columns Start & Len.

    = Table.AddColumn(#”Changed Type”, “Just_Name”, each Text.Middle([Untrimmed],Int64.From(Text.Format(“#[Start]”,_)),Int64.From(Text.Format(“#[Len]”,_))))

    Just_Name results:
    (sorry, no Paul)

    Perhaps you’re thinking why do this? I want to build a generalized function that trims based on parameter values stored in a table. Any thoughts?

  6. Hi Chris,
    I have to pass dated in mm/dd/yyyy format in M query. I am pulling data using complex query (6 tables) from Netezza and want to make it dynamic where use can select the dates to pull the data.
    Catch is, date column is not there in the top level while it is available in the underlying tables where dates needs to be passed. Due to underlying factor queryfilter as well as parameters are not working.
    Can you please guide.

Leave a Reply

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

%d bloggers like this: