Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Function Missing dates + api cross rates

Hi all,   I'm using the ECB api to retrieve historical cross rates. I'm using the ECB Api together with a missing date function in order to populate saturday and sunday with rates from friday. This...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

You can try to fill down by group.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBJDoUgEAXvwloNPYC6JP/fwnj/azjwNBHeiqQqobtr24JGlTGNOQxBJo/z+f7KP+zDqy7U04XSlVKJ9XcR4gSTM3EKl4iz6syIc8xT4hJfMXM8YwrpgmR2lSilLXYa986g2heuDKKZ3fc1qiaz+7xGKVQ/GsGUfeh0g0RpprSebPqstB8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Rate = _t, Currency = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Rate", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Currency"}, {{"Count", each Table.FillDown(_,{"Rate"}), type table [Date=nullable date, Rate=nullable number, Currency=nullable text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Rate"}, {"Count.Date", "Count.Rate"})
in
    #"Expanded Count"

 

Reference: Conditionally Fill Down Data in Excel with Power Query

 

Best Regards,

Stephen Tao

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    Sorry for replaying late.

    I was looking at your solution and I'm not sure it solves my problem fully.

    It is filling in the gap between Friday and Monday nicely, in order to do that it assumes I have the Monday rate ready, which is kind of the problem unfortunately. Or at least it is a problem each current Monday, when the latest rate is the prevoius Friday but I don't have a Monday rate, in this case I would like to fill Saturday and Sunday without having to wait for Monday rate to be updated.

     

    //Alex