Target Currency format is EURO (5.000,00) & Input source has combination of collapsed formats

[Suresh777]

Hello Techies,

I am beginner in Power Query however I need to solve some important issues which face in my work.

We are using EURO currency foramat. Ex. for Five Thousand Euros and cents we write as 5.000,00 €. In general english format is 5,000.00 $.

I have to consider tables from standard PDF invoice vouchers. Many of the table I get as actual format in EURO what I need. But few of them people used to make it in English format.

When I call the table in Power Query the english format shows error then I need to make manual replace like below. Sometimes I failed to observe all the errors and it takes huge manual efforts.

I searching for a formulae to filter the error value automatically instead manual work.

Thanks for your time and help.

Regards

Suresh

Whatsapp (+49 15166330777)

[ronrsnfld · Accepted solution]

All of your examples show a number with two decimal places.

If all of the values will have two decimals then:

• Initially set the data type to text.
• Remove all of the separators
• Convert back to a number
• divide by 100

Source formatted as text

let

//change next line to reflect actual data sournce
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//ensure price column is set as text
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit Price", type text}}),

//remove all separators and divide by 100
    #"Remove Separators" = Table.TransformColumns(#"Changed Type",{"Unit Price", 
        each Number.From(Text.Remove(_,{",","."})) / 100}),

//set data type to currency (or number, if you prefer)
    #"Set Type" = Table.TransformColumnTypes(#"Remove Separators",{"Unit Price", Currency.Type})
in
    #"Set Type"

Results as currency

[Suresh777]

Hi,

It is really simple and powerful solution. 🍻

GREAT !

Thanks for your help.

Regards

Suresh

Regards

Suresh

[Suresh777]

I have tried it. It doesn't work. It only accepts the correct input consisting comma. Please see the samples below.

Power query requires the below samples as correct with comma.

But,

Original input consists below error. It supposed to be 1,45. 

After applying the step change type to Fixed Decimals (or) Changing Local --> Fixed Decimal as German.

Sometimes I am failed to recognise these kind of errors and it leads to error in total. The perfect idea would be define the fool proof system in the input system.

However It would be great if we identify a solution for these kind of errors.

Thanks

Suresh

[Greg_Deckler]

@Suresh777 Have you tried right-clicking the column and used "Change Type | Using Locale..."?