Forum Discussion
How to remove decimals?
Hi Team,
how to remove decimals for fixed values,
Required output as below like
Hi sekhar438 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "Advanced Editor" to copy and paste the following code, check the steps in the right step bar.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtYzNfJVitWJVjLVszCHsoz0DAyAzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sales = _t]), #"Split Column by Character Transition" = Table.SplitColumn(Source, "Sales", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Sales.1", "Sales.2", "Sales.3"}), #"Added Conditional Column" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if [Sales.2] = ".00" then null else [Sales.2]), #"Merged Columns" = Table.CombineColumns(#"Added Conditional Column",{"Sales.1", "Custom", "Sales.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Sales.2"}) in #"Removed Columns"(3) Then the result is as follows.
You can refer to the following documents that may be helpful to you:
3 Ways to Truncate Numbers (Remove Decimals) in Power Query (gorilla.bi)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi sekhar438 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "Advanced Editor" to copy and paste the following code, check the steps in the right step bar.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtYzNfJVitWJVjLVszCHsoz0DAyAzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sales = _t]), #"Split Column by Character Transition" = Table.SplitColumn(Source, "Sales", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Sales.1", "Sales.2", "Sales.3"}), #"Added Conditional Column" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if [Sales.2] = ".00" then null else [Sales.2]), #"Merged Columns" = Table.CombineColumns(#"Added Conditional Column",{"Sales.1", "Custom", "Sales.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Sales.2"}) in #"Removed Columns"(3) Then the result is as follows.
You can refer to the following documents that may be helpful to you:
3 Ways to Truncate Numbers (Remove Decimals) in Power Query (gorilla.bi)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply Neeko Tang
If I used the above script, deleted all columns of data
Hi sekhar438 ,
This is just an example. You can add other data columns to the source. Or you can refer to the steps in the step column on the right in your own table to split the [Sales] column, then add a custom column, and finally merge it.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
