Forum Discussion
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" feature to insert Carriage Returns and Line Feeds in between. However, some of my data are dollar values and/or numbers that need thousands commas, but after I complete the "Merge Columns" process, all of my numbers display without US currency symbols or commas.
What can I add to the string below such that the outputs for [#"OData_3-Mile 65_x002"] include commas, and the outputs for [#"OData_3-Mile Average"] and [#"OData_3-Mile Medium_"] include currency symbols and commas?
= Table.AddColumn(#"Changed Type6", "Merged", each Text.Combine({Text.From([#"OData_3-Mile 65_x002"], "en-US"), Text.From([#"OData_3-Mile Average"], "en-US"), Text.From([#"OData_3-Mile Medium_"], "en-US")}, "="), type text)
Right now, the output above yields results like: 10924=81375=150556 ....but I need it to display as 10,924=$81,375=$150,556
Thank you!
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!
4 Replies
This is typically something you do once you load the data in (after chaning the data type to number). If your using Power Bi, simply click the column in the right "Fields" panel, and then on the top ribbon click the $ button.
If you really need to do this in Power Query, then you can use Number.ToText(Number.From(Text.Combine(...)), "C", "en-us")
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")).
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!
