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 wh...
Need Help
Tips and Tricks
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