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)
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”:
letMyRecord = [Animal="cat", Furniture="mat"],GetAnimal = MyRecord[Animal]inGetAnimal
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}",
"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)},
"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)},
"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:
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:
letMDXQueryTemplate ="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])inRunQuery
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.