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

image

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”:

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

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

Text.Format(

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

{5,8,9})

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

Text.Format(

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

"en-us") 

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

Text.Format(

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

"fr-fr") 


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:

image

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]", _)

image

Returns a table that looks like this:

image

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:

let
    MDXQueryTemplate = 
	"SELECT#(lf)
	{[Measures].[Internet Sales Amount]} ON 0,#(lf)
	NON EMPTY#(lf)
	[Date].[Calendar Year].[Calendar Year].MEMBERS ON 1#(lf)
	FROM#(lf)
	[Adventure Works]#(lf)
	WHERE(
	STRTOMEMBER(
	""[Customer].[Country].&[#[Country]]"", 
	CONSTRAINED))",
    ReplaceCountryParameter = 
	Text.Format(
		MDXQueryTemplate, 
		[Country="Australia"]),
    RunQuery = 
	AnalysisServices.Database(
		"localhost", 
		"adventure works dw 2008", 
		[Query=ReplaceCountryParameter])
in
    RunQuery

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.

7 thoughts on “The Text.Format() Function In Power BI/Power Query M

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

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

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