Forum Discussion

arpost's avatar
arpost
Post Prodigy
4 years ago
Solved

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

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?

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      arpost
      Post Prodigy
      4 years ago

      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?