Forum Discussion
Troubleshooting decimals in Excel when importing
Dear All, good morning.
I would like to write a new column in Power Query that will allow me to troubleshoot decimals, mainly caused by "," and "." in the excel.
I have a column like this, below the desired result.
| Weight(lbs) | Desired result |
| 3,01 | 3.01 |
| 3.01 | 3.01 |
| 3.000,00 | 3000.00 |
| 3,000.00 | 3000.00 |
| 3 | 3.00 |
| 20 | 20.00 |
| 3.000.000,00 | 3000000.00 |
| 3,000,000.00 | 3000000.00 |
How can I do it in Power Query?
Thanks in advance!
HI Anonymous,
You can add a custom column with the 'try otherwise' structure and 'Value.FromText' function with a culture option to handle text strings with two different formats, then you can simply change the converted results to common decimal values.
#"Added Custom" = Table.AddColumn(Source,"Custom",each try Value.FromText([#"Weight(lbs)"],"de-DE") otherwise Value.FromText([#"Weight(lbs)"],"en-US"))Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtYxMFTSUTLWA1KxOtEQBirfwEDHwAAkBmQBeRBhHSgHTRiiFcIxAskaIeT0IAqRTEM3ENVQmGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Weight(lbs)" = _t, #"Desired result" = _t]), #"Added Custom" = Table.AddColumn(Source,"Custom",each try Value.FromText([#"Weight(lbs)"],"de-DE") otherwise Value.FromText([#"Weight(lbs)"],"en-US")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Desired result", Currency.Type}, {"Custom", Currency.Type}}) in #"Changed Type"Reference links:
Value.FromText - PowerQuery M | Microsoft Docs
Error handling for Power Query connectors - Power Query | Microsoft Docs
Regards,
Xiaoxin Sheng
1 Reply
HI Anonymous,
You can add a custom column with the 'try otherwise' structure and 'Value.FromText' function with a culture option to handle text strings with two different formats, then you can simply change the converted results to common decimal values.
#"Added Custom" = Table.AddColumn(Source,"Custom",each try Value.FromText([#"Weight(lbs)"],"de-DE") otherwise Value.FromText([#"Weight(lbs)"],"en-US"))Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtYxMFTSUTLWA1KxOtEQBirfwEDHwAAkBmQBeRBhHSgHTRiiFcIxAskaIeT0IAqRTEM3ENVQmGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Weight(lbs)" = _t, #"Desired result" = _t]), #"Added Custom" = Table.AddColumn(Source,"Custom",each try Value.FromText([#"Weight(lbs)"],"de-DE") otherwise Value.FromText([#"Weight(lbs)"],"en-US")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Desired result", Currency.Type}, {"Custom", Currency.Type}}) in #"Changed Type"Reference links:
Value.FromText - PowerQuery M | Microsoft Docs
Error handling for Power Query connectors - Power Query | Microsoft Docs
Regards,
Xiaoxin Sheng