Forum Discussion

PCR324's avatar
PCR324
New Member
3 years ago
Solved

Power Query format numbers properly after merge

I'm in the process of creating a Table visual in Power BI.  For certain columns, I used the "Merge Columns" feature to combine data from multiple fields into a new column; I then used "Replace Values...
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi PCR324,

     

    try something like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TckxEoBACEPRu1BTAIlrrR6D4f7XWNDG6k1+MuUSFQ8zawOf4Fiack/1f1U5Fsj3fXoRAU6lB9p1zq7a", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Column2]) & "=" & Text.From([Column3]) & "=" & Text.From([Column4]), type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.ToText([Column2], "#,##0") & "=" & Number.ToText([Column3], "$#,##0") & "=" & Number.ToText([Column4], "$#,##0"))
in
    #"Added Custom1"

     

    Kind regards,

    John

  • PCR324's avatar
    PCR324
    3 years ago

    John, 

     

    You are a lifesaver.  From your recommendation, I realized that all I had to do was change this (for example) Text.From([#"OData_3-Mile Medium_"], "en-US" to this Number.ToText([#"OData_3-Mile Medium_"], "$#,##0"

     

    Can't thank you enough!

PCR324's avatar
PCR324
New Member
3 years ago

Thank you kindly for your reply.  However, in Power BI, it is my experience that currency formatting applied to a given field does not carry forward with the data after a currency field (column) is merged with another column via Power Query Editor.  Am I missing something here?  Also, I was unable to get your suggested Power Query language to work (Number.ToText(Number.From(Text.Combine(...)), "C", "en-us")).

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi PCR324,

     

    try something like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TckxEoBACEPRu1BTAIlrrR6D4f7XWNDG6k1+MuUSFQ8zawOf4Fiack/1f1U5Fsj3fXoRAU6lB9p1zq7a", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Column2]) & "=" & Text.From([Column3]) & "=" & Text.From([Column4]), type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.ToText([Column2], "#,##0") & "=" & Number.ToText([Column3], "$#,##0") & "=" & Number.ToText([Column4], "$#,##0"))
in
    #"Added Custom1"

     

    Kind regards,

    John

    • PCR324's avatar
      PCR324
      New Member
      3 years ago

      John, 

       

      You are a lifesaver.  From your recommendation, I realized that all I had to do was change this (for example) Text.From([#"OData_3-Mile Medium_"], "en-US" to this Number.ToText([#"OData_3-Mile Medium_"], "$#,##0"

       

      Can't thank you enough!