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

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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

         
         
         
         
  • KubeM85's avatar
    KubeM85
    New Member
    24 days ago

    I am from Zambia and my country initially used to use a K to indicate it's currency the Kwacha we underwet a rebasing and the new way to indicate the Kwacha now is ZMW can that be added to the currency options?