Forum Discussion
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...
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
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!
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
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!