Currency notation missing from excel source

[Anonymous]

Hi Experts,

In my excel datasource, when the currency column is like 5 EUR, it is getting imported in Power BI as 5, meaning that all the currency notation is not getting displayed in PBI for that column. This single column can have different currencies as shown in below snip. How do we get the currency notation in addition to the value in PBI as we see in Excel ?

PRICE

1.00 CNY

1.00 CNY

1.00 JPY

1.00 JPY

1.00 SEK

1.00 CNY

1.00 CNY

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 SEK

1.00 SEK

1.00 SEK

1.00 SEK

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 SEK

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

4.00 EUR

1.00 SEK

1.00 EUR

2.00 EUR

4.00 EUR

2.00 EUR

3.00 EUR

1.00 EUR

6.00 EUR

2.00 SEK

2.00 USD

3.00 EUR

1.00 EUR

2.00 EUR

10.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

1.00 EUR

2.00 EUR

2.00 EUR

Thanks & Regards,

Kesavan.

[tex628 · Accepted solution]

To my knowledge you will not be able to import the data with the varied currency data format. I belive that you will need to add an additional column in the excel source data that holds the currency. 

Br,
J

[harshnathani]

Hi @Anonymous ,

You can select the Currency under Column Tools.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani ,

If i do that, all the rows will be tagged to that selected currency..But the source data has different currencies in that single column as i showed in screenshot. I need to show as is what i get in excel, as seen in screenshot, no need to do any currency conversions etc., Any ideas ?

Thanks & Regards,

Kesavan.

[harshnathani]

Hi @Anonymous ,

Create a Calculated Column

Currency Convert =

Var _s = SEARCH(" ",'Table'[Column1],,LEN('Table'[Column1]))

var _r = TRIM(RIGHT('Table'[Column1],(Len('Table'[Column1]) - _s)))

Return

SWITCH(

TRUE(),

_r = "CNY",FORMAT('Table'[Amount],"""¥""#,0.00"),

_r = "JPY",FORMAT('Table'[Amount],"""¥""#,0.00"),

_r = "EUR", CONCATENATE(UNICHAR(8364),FORMAT('Table'[Amount],"#,0.00")),

_r = "USD",FORMAT('Table'[Amount],"""$""#,0.00"),

_r = "SEK",FORMAT('Table'[Amount],"#,0.00 kr")

)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani ,

I think you have gone to the next level. But my quesion is different. Sorry if I didn't explain it well. Let me repeat now.

In my excel the values are like below for column 'PRICE'. 

[Anonymous]

Hi,

Oh, the screenshots are not getting displayed in previous post.

Below is how the excel data looks like for column PRICE.

After importing in PBI, the column PRICE looks as below.

Is this the normal behavior? Currency notation is lost in PBI.

Is there any way in PBI to display the values along with currency notation, similar to the way we see in excel.

Mvh,

Kesavan.

[harshnathani · Accepted solution]

Hi @Anonymous ,

I think this is default behaviour. 

You will need to create a column with the country name and then use this formula.

Regards,

Harsh Nathani

[tex628 · Accepted solution]

To my knowledge you will not be able to import the data with the varied currency data format. I belive that you will need to add an additional column in the excel source data that holds the currency. 

Br,
J