Forum Discussion
How to work with a column containing numbers in different currencies
Hi,
I have around 50 columns of data and it is not practical to make currency conversions for each of these columns.
The columns in the database hold values in different currencies.
Ex: Some values in Column A are in USD while some are in Euro and some others in INR.
When I import values to Power BI (Power Query), I noticed that the values are considerednumbers/ decimals and the currency associated with it is lost.
Please refer the snapshots below:
Data Source in Excel
Power Query - Currency Info is lost
Power Pivot - Applies one Currency
Is this a Bug?
Is there a way I can retain Currency information in the column
or should I get all data as Text, split to retain Currency info and use the values?
Thanks & Regards,
Mannu
Anonymous Yes, you need to consider that field as Text data type to retain the currency symbols as well (As the data contains different currency types, you can't assign a particular currency type to mixed data).
Split the value into two fields one containing Currency Type/Symbol and another containing the value. If you need to do any further analysis or have a lookup table for Conversion and convert all values into a single currency type.
2 Replies
- PattemManohar
Community Champion
Anonymous Yes, you need to consider that field as Text data type to retain the currency symbols as well (As the data contains different currency types, you can't assign a particular currency type to mixed data).
Split the value into two fields one containing Currency Type/Symbol and another containing the value. If you need to do any further analysis or have a lookup table for Conversion and convert all values into a single currency type.
Its bad if we need to get all columns as text (with Currency symbol), then make changes to each and every currency column using Column with examples/ splitting the column ..etc.
A currency type should behave as a decimal number (wrt sum/avg ..etc) but should be able to identify currency symbol and apply default formatting rules for the currency (dot, comma, Currency symbol ..etc)