Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Currency formatting in power query editor

Hi,

I am trying to format a field "Actuals" to currency in query editor. Unlike desktop, I don't see how to set the format for the field to currency. I am trying to do this in power query so that when I change my datasource, the measures based on that field "Actuals" don't lose its formatting.  Is there a way to format the filed in m query to currency? 

 

Also, Is there a different way to change the datasource so that I don't lose the formatting on my measures everytime I change the datasource?

 

Need Help
Tips and Tricks

3 Replies

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    5 years ago

    For Column type - Currency, try this:

     

    Table.TransformColumnTypes(YourTable,{{"YourColumnName", Currency.Type}})

     

    If you have your Source table in raw format, without headers, this is kind of dynamic solution.

    In example case, i have used "Sales" string as identifier for column dedicated to currency. I am pretty sure that you have some unique identifier also. Just replace it. 

    Until somebody else provides a neater solution, try it. Replace the Source step with your table of course 🙂

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZK9bsIwFEbfxXMG/xZ7DVUHpEhIsKCIwU1vCCrClbGRePs6TqSCE0edbuwcX5/7JXWNttZ8+cahAu0fPxBKZa6uC/UA2oay0xe4oWNRoz3osC7hDlafwl6BNvrqtX2EJ4oJGwqJbOmdg/74uz5HYIISJXhED+bkrVtCqVAiomvTtgBLErzvzNgq4lvjLy6+qUC7OXRFxX9G61EqyeC78ecmdfiAT/vqzLEYHez3n8CUo5Sy+cSmLMH0bX6wVzbqkjHedQdwg2zfYTQ+sCVAu9yU42wGlbZN9zSYImymaUIRkdNMQYlx5lM9kb2kUCr/syQsleP1k0QTkEgZoj/+Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    Index = Table.SelectRows( Table.AddIndexColumn(Table.AddColumn(Table.FromList(Table.ToRows(Table.Transpose(Source)),Splitter.SplitByNothing()), "Custom", each List.Contains([Column1],"Sales")), "Index",0,1,Int64.Type),each ([Custom] = true))[Index]{0},
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ToCurrency = Table.TransformColumnTypes(Promoted,{{Table.ColumnNames(Promoted){Index}, Currency.Type}})
in
    ToCurrency

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Anonymous 

    You can use format() in Desktop to set measure format to currency. For example: Measure= FORMAT( sum([data]), "Currency")

    FORMAT function (DAX) - DAX | Microsoft Docs

     


    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks, it did let me carry the currency format on measures when I am changing the datasource. But looks like this method is changing the feild to text and therefore when I am trying to do a feild formatting for negative values to be displayed in red colour, its not working.