Forum Discussion

Oryan's avatar
Oryan
New Member
4 years ago
Solved

POWER QUERY CURRENECY

Hi friends, I have a table containing the value of coins at the rate of Euro with all dates history.  (see photo)

 

 

see here all value date history :

 

 

The target of this  is to display exchange rates from EUR, GBP and USD to all other currencies for all dates [ INCLUDE WEEKENDS]

what i need to do now ? i have all dates and all currencies on euro

Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Oryan ,

    Sounds like you want to calculate the daily rate for each currency, you can try this query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFNDoQgDIbhu3RtIm0pyHJ+PAXx/teQYoLMfG5YPCG8UGqlFy3EmtoqQWTlVZiOpdK7iQnwx7kAf5sUBt6bZAPuTQ6Ty2hy3MA9mhnYo4q7PZoMuEclTq4jWgKwN8WAvSkJeIeb6x2dt8fRTArszRSB+3A34N4UPPz60Ty53dP9mZc9T9eeX2qjan+HHyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source Currency" = _t, #"Exchange Rate" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source Currency", type text}, {"Exchange Rate", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"Source Currency"}, 
            {
                {"Data", each 
                    let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in
                        Table.AddColumn(
                            tab,"New Rate",
                            (x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Exchange Rate]
                                    otherwise null 
                        )
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Exchange Rate", "Date", "New Rate"}, {"Exchange Rate", "Date", "New Rate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Exchange Rate", Int64.Type}, {"Date", type date}, {"New Rate", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Rate", each if [New Rate] = null then null else ([New Rate] - [Exchange Rate]) / [Exchange Rate],Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"New Rate"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}, {"Source Currency", Order.Ascending}})
in
    #"Sorted Rows"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    We need data and expected output. Data in CSV or XLSX format, and expected output can be a picture mocked up in Excel.

     

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Oryan ,

    Sounds like you want to calculate the daily rate for each currency, you can try this query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdFNDoQgDIbhu3RtIm0pyHJ+PAXx/teQYoLMfG5YPCG8UGqlFy3EmtoqQWTlVZiOpdK7iQnwx7kAf5sUBt6bZAPuTQ6Ty2hy3MA9mhnYo4q7PZoMuEclTq4jWgKwN8WAvSkJeIeb6x2dt8fRTArszRSB+3A34N4UPPz60Ty53dP9mZc9T9eeX2qjan+HHyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source Currency" = _t, #"Exchange Rate" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source Currency", type text}, {"Exchange Rate", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"Source Currency"}, 
            {
                {"Data", each 
                    let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in
                        Table.AddColumn(
                            tab,"New Rate",
                            (x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Exchange Rate]
                                    otherwise null 
                        )
                }
            }
        ),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Exchange Rate", "Date", "New Rate"}, {"Exchange Rate", "Date", "New Rate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Exchange Rate", Int64.Type}, {"Date", type date}, {"New Rate", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Rate", each if [New Rate] = null then null else ([New Rate] - [Exchange Rate]) / [Exchange Rate],Percentage.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"New Rate"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}, {"Source Currency", Order.Ascending}})
in
    #"Sorted Rows"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.