Change Currency Symbol at Power Query level

[qbarnes]

Hello Everyone, checking with the experts on how to accomplish the task of changing the currency symbol for monetary fields, based on location - at the Query level.  

Most of my research identified the incorporation of another table, in consideration of currency exchange rates.  But that's not my path.      My report is simply tracking loan performance, so the loan amounts remain flat in every location. 

Our "Entity Location" includes:  Canada, United Kingdom, and USA.

Is there a way to adjust the currency "Symbol" based on the Entity Location, at the Power Query Level?  Note: My data includes financials from 3 different countries in the same column.

Desired Output:

Entity Location |  Original Bal  |  Present Bal

Canada             |  $905,000 |  $759,000

UK                     | £900,000 |  £652,000

USA                   |  $915,000 |  $710,000

P.S.  If there's a way to apply this logic to an entire table, i'll take that as well.

[v-yingjl · Accepted solution]

Hi @qbarnes ,

Actually you can add currency symbol in power query but it will be a text column so that we cannot use it to calculate unless the column value has been calculated in advance then we convert it to a text column.

Power query code may like this:

Custom1 = Table.AddColumn(
        #"Changed Type",
        "Orignal",
        each if [Entity Location] = "Canada" or [Entity Location] = "USA" 
             then 
                "$ " &  Text.From([Original Bal]) 
                    else if [Entity Location] = "UK" 
                        then 
                            "£ " &  Text.From([Original Bal]) 
                                else null
    ),
    Custom2 = Table.AddColumn(
        Custom1,
        "Present",
        each if [Entity Location] = "Canada" or [Entity Location] = "USA" 
             then 
                "$ " &  Text.From([Present Bal]) 
                    else if [Entity Location] = "UK" 
                        then 
                            "£ " &  Text.From([Present Bal]) 
                                else null
    )

Attached my sample file that you can refer: currency symbol.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @qbarnes ,

Does it work if you load a table with currency code and symbols ? You will need to create a currency code on your base table. After that just join them and merge the columns.

After this transformation, this column will be like a text, no calculations will be possible.

[qbarnes]

Hi @camargos88 , 

That's a pretty slick workaround, but I would need to maintain the ability to run some several calculations.  

[FrankAT]

Hi @qbarnes,

does this solve your request?

Regards FrankAT