Forum Discussion

Sylvain74's avatar
Sylvain74
Icon for Helper III rankHelper III
9 years ago
Solved

Convert columns on the fly as per selected parameters

Hi Guys,

 

I'm starting in Power BI development and I need to create a report which convert quantity and amount from original respective unit to new units. 

For example I have some quantities which can be in ever weight unit such as Kg, LB or Mt and as well amounts in USD, EUR ,GBP...

Each day those amounts are valuated, so each day same records are created with valorized amounts.

 

I would like to have parameters or slicers to select a target weight unit and a target currency.

In my data model, each records from my main table has a relation to an exchange rate table containing all currency rates combination for each valution date. The relationship is done on the original currency and the valuation date but I don't know how to apply the selected target currency to the output currency of my curreny rates table.

I would like to do something like this I was used to do in sql:

 

select

  original amount

,original ccy

,@destination ccy

,original amount * exchange rate

from myPositions P

inner join myXRates X on P.valuation date = X.valuation date and P.original ccy = X.Input ccy and X.Output ccy = @Output Ccy

 

Thank you very much for your help.

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Sylvain74,

     

    You can try to use below function to get data from sql with parameterized query:

     

    let
    loadDataBase=(ServerName as text,DataSource as text, destination as text, Output as text) as table=>
    let
        SQLQuery = "select  original amount,original ccy,"&destination&" ccy ,original amount * exchange rate from myPositions P
        inner join myXRates X on P.valuation date = X.valuation date and P.original ccy = X.Input ccy and X.Output ccy = "&Output&" Ccy",
        Source = Sql.Database(ServerName, DataSource,[Query=SQLQuery])
    in
        Source
in
    loadDataBase

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Sylvain74,

     

    You can try to use below function to get data from sql with parameterized query:

     

    let
    loadDataBase=(ServerName as text,DataSource as text, destination as text, Output as text) as table=>
    let
        SQLQuery = "select  original amount,original ccy,"&destination&" ccy ,original amount * exchange rate from myPositions P
        inner join myXRates X on P.valuation date = X.valuation date and P.original ccy = X.Input ccy and X.Output ccy = "&Output&" Ccy",
        Source = Sql.Database(ServerName, DataSource,[Query=SQLQuery])
    in
        Source
in
    loadDataBase

     

    Regards,

    Xiaoxin Sheng

    • Sylvain74's avatar
      Sylvain74
      Icon for Helper III rankHelper III
      9 years ago

      Hi Xiaoxin,

       

      Sorry for my late answer.

      Thank you very much for your help I will test it and hope it will help me to sort out my issue.

      Will let you know if I have any additional questions.

       

      Thanks again

      Sylvain