Is it possible to format numbers using text format strings in Power Query?

[arpost]

Greetings, all. I have a scenario where I need to push some formatting to Power Query but am finding I can't identify how to do that. Here's the situation.

I have a range of dollar amounts and am wanting to format those in specific ways. Here's an example in the Band column:

Previously, I did this using DAX with some if logic; here's a snippet:

Format([Start],IF([Start]>=1000000000,"$#,,,.##B","$#,##0,,M"))

Now, I want to achieve the same result in Power Query but can't seem to identify the correct M function to apply these types of text format strings. Anyone have ideas?

[AlexisOlson · Accepted solution]

You can write a custom column using Text.Format like this:

if [Start] >= 10000000
then Text.Format("$#{0}M to #{1}M", {[Start]/1000000, [End]/1000000})
else "<$10M"

[arpost]

That works a treat! Thanks. I know with the FORMAT function in DAX there are different codes/format strings (link), is there a list somewhere of which you know for the Text.Format function in M?

[AlexisOlson]

Not that I know of.