在Power Query中将格式错误的文本转换为数字
我有一个从业务系统下载的价格专栏，根据国家的不同，数字有不同的格式，例如：
1.260,14 -> this is seen as text by Excel
1,280.14 -> this is seen as text by Excel
1280.14 -> this is the only correct one, seen as number我希望Power Query将所有内容转换为数字，在这种情况下，这意味着所有3个数字都应该是："1280.14“
回答 4
Stack Overflow用户
发布于 2016-03-18 22:36:44
1)在小数位置拆分列:按字符数拆分列:2-尽可能远地向右一次
2)第一列:替换".“按"“(无)
3)第一列:将"，“替换为"”(无)
4)用“”合并这两列。作为分隔符，并更改为十进制格式
Stack Overflow用户
发布于 2016-03-19 02:45:03
如果您知道哪些文档来自哪个国家/地区，那么在使用Table.TransformColumnTypes时也可以考虑区域设置。您可以右键单击列并选择Change Type | Using Locale...。这将生成类似于Table.TransformColumnTypes(Step, {{"Column Name", Currency.Type}}, "locale name")的内容。
Stack Overflow用户
发布于 2016-07-26 20:03:51
这是一个Power Query函数，我刚刚创建了一个函数，将混合格式的文本转换为数字。它将最后出现的点或逗号视为小数分隔符，只要该字符在文本中只出现一次。
要使用此功能，请转到"Power Query“功能区选项卡，单击"From Other Sources”> "Blank Query“。然后转到“高级编辑器”，将下面的脚本复制粘贴到编辑器中并保存。然后，您可以返回到主查询，并单击"Add Column“> "Add Custom Column”。"=“后面的公式是：toNumber([column name])。
let
toNumber = (text) =>
let
//remove characters that occur more than once as they can't be decimal separators
text1 = if List.Count(Text.PositionOf(text, ",", Occurrence.All)) > 1
then Text.Replace(text, ",", "") else text
, text2 = if List.Count(Text.PositionOf(text1, ".", Occurrence.All)) > 1
then Text.Replace(text1, ".", "") else text1
//if there are still more than one potential decimal separator, remove the kind that occurs first
//let's assume the last one is the actual decimal separator
, text3 = if List.Count(Text.PositionOfAny(text2, {",","."}, Occurrence.All)) > 1
then Text.Replace(text2, Text.At(text2, Text.PositionOfAny(text2, {",","."}, Occurrence.First)), "")
else text2
//cast as number (try different decimal separators)
, number =
try Number.ToText(Number.From(Text.Replace(text3,",",".")))
otherwise Number.ToText(Number.From(Text.Replace(text3,".",",")))
in
number
in
toNumberhttps://stackoverflow.com/questions/36084392
复制相似问题
腾讯云开发者
