Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to Solution.
All of your examples show a number with two decimal places.
If all of the values will have two decimals then:
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
All of your examples show a number with two decimal places.
If all of the values will have two decimals then:
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
Hi,
It is really simple and powerful solution. 🍻
GREAT !
Thanks for your help.
Regards
Suresh
Regards
Suresh
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
@Suresh777 Have you tried right-clicking the column and used "Change Type | Using Locale..."?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.