Extract 2 (or 1) decimal numbers from string

[Anonymous]

Hi guys, 

I have not been able to solve this.

I have strings of text, which either contain one or two decimal numbers (amounts). I need to extract these numbers and in the case there are 2, add them up. 

Examples:

KO#K7768_PP#3109_EU#6761,95

 =6761,95

KO#310027_EU#3700,5
KO#31006_EU#300,5

= 4100,00

KO#31049_EU#7.000,50_PP#31310_EU#1950,00

=8950,50

KO#C98 _PP#31002_EU#2.000,36_EU2#1500,14

=3500,50

I am only interested in the numbers with the decimals. Unfortunately there is not a consistent delimiter (EU#, EU2# or just #) and not always two numbers are present.

Should I use dax or M for this, and what is a viable solution for the long term? The numbers I am interested in always contain two decimal points, but the way they are stored in the string is inconsistent.

[Anonymous]

HI @Anonymous ,

I can't find the regular in your text, it is hard to extract specific values from your records.
In my opinion, I can use power query formula to extract all numeric string(number code and decimal values) but can't accurate recognize which part you needed.

Maybe you can refer to following blog to replace other characters to extract numeric value:

Keep Only Numbers in Power Query

M query characters list who need to be removed:

clist= List.Transform({65..90,97..122,35,95}, each Character.FromNumber(_))

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for the reply. I managed to extract the first transaction with helper columns:

First trim the string and extract everything after the first EU#.
Then extract everything (the numbers I need) until the first ",".
Then extract the two numbers after that ",".
Concatenate the two in a new column.

The last transaction is always at the end. So it is easy to extract the two numbers (the decimals of the transaction) until the second ","  (e.g. 1000, "23").
Now I only need the body part of the transaction (e.g. "1000", 23). However, there is no consistent delimiter before that number. Is it possible to create a line in which the delimiter you seek is set up as anything except for a number? That would solve my problem.

[Anonymous]

Hi @Anonymous ,

>>Is it possible to create a line in which the delimiter you seek is set up as anything except for a number? 

AFAIK, power query Split functions still not works on dynamic separator who recognize by its text characters. (auto recognize function will check your records and try to find out similar separators)

Regards,

Xiaoxin Sheng