CODEWITHSUNDEEP
excelexcel-formulapowerqueryexcel-2016
MmVv
MmVv

Reputation: 553

Convert negative text to decimal number in Power Query

How to convert negative numbers stored as text into decimal using Power Query?

I am having text data generated from a software in this format:

11334;-          6,200;10;"040";"MAT";11;"CLEANSORB CS3G";"Abgang in Produktion";"2-GR";ja;446,600;"MatSchein Abg in Fertig";"MAT";"";07.10.2020 08:13:49;"";"PPA";3646;10,0;02;17.529,820;07.10.2020;230;2.246,600;"99";17.529,820
11334;-          2,200;10;"040";"MAT";11;"CLEANSORB CS3G";"Abgang in Produktion";"2-GR";ja;446,600;"MatSchein Abg in Fertig";"MAT";"";07.10.2020 08:13:49;"";"PPA";3646;10,0;02;17.529,820;07.10.2020;230;2.246,600;"99";17.529,820
11334;-          126,200;10;"040";"MAT";11;"CLEANSORB CS3G";"Abgang in Produktion";"2-GR";ja;446,600;"MatSchein Abg in Fertig";"MAT";"";07.10.2020 08:13:49;"";"PPA";3646;10,0;02;17.529,820;07.10.2020;230;2.246,600;"99";17.529,820
11334;-          15,000;10;"040";"MAT";11;"CLEANSORB CS3G";"Abgang in Produktion";"2-GR";ja;446,600;"MatSchein Abg in Fertig";"MAT";"";07.10.2020 08:13:49;"";"PPA";3646;10,0;02;17.529,820;07.10.2020;230;2.246,600;"99";17.529,820
11334;          10,200;10;"040";"MAT";11;"CLEANSORB CS3G";"Abgang in Produktion";"2-GR";ja;446,600;"MatSchein Abg in Fertig";"MAT";"";07.10.2020 08:13:49;"";"PPA";3646;10,0;02;17.529,820;07.10.2020;230;2.246,600;"99";17.529,820

And when I try to use this with Power Query to convert to decimal number I am getting an error:

enter image description here

enter image description here

As you can notice there are all of a sudden lot of empty spaces after the (minus) sign, and if I try to do >replace< option it is working partly because of different number of spaces... I tried to change locals, format, file origin, all those options that I am capable of but nothing...

Can someone help why and what is causing this issue?

p.s After downloading this file using software that is generating this text/csv files, it firstly (automatically) opens file in read mode (where looks all fine) but I need to save it in .xlsx for instance and the use power query...so you can imagine that is always manual work...and it is strange why PowerQ cant read this file > also I tried in Power BI but same thing...

Thanks in advance.

Upvotes: 1

Views: 846

Answers (1)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

Try this:

= Table.TransformColumns(Source, {"ColumnWithSpaces", each Number.From(Text.Remove(_,{" "})), type number})

Upvotes: 1

Related Questions