Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Currency notation missing from excel source

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 tha...
Need Help
  • tex628's avatar
    tex628
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    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'. 

     
    • tex628's avatar
      tex628
      Community Champion
      6 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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