Function Missing dates + api cross rates

[Anonymous]

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 solution looks like this:

let
    Source = Csv.Document(Web.Contents("https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.GBP+CAD+USD+JPY+CHF.EUR.SP00.A?startPeriod=2017-01-01", [Headers=[Accept="text/csv"]]),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"CURRENCY", "TIME_PERIOD", "OBS_VALUE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"CURRENCY", type text}, {"TIME_PERIOD", type date}, {"OBS_VALUE", type number}}),
    #"Renamed Columns" = Table.RenameColumns( #"Changed Type", {{"CURRENCY","Currency"}, {"TIME_PERIOD", "DateTo"}, {"OBS_VALUE", "Rate"}}),
    #"Added DateFrom" = Table.AddColumn(#"Renamed Columns", "DateFrom", each fnMissingDates(#"Renamed Columns", [Currency], [DateTo])),
    #"Expanded DateFrom" = Table.ExpandListColumn(#"Added DateFrom", "DateFrom"),
    #"Removed DateTo" = Table.RemoveColumns(#"Expanded DateFrom",{"DateTo"}),
    #"Renamed DateFrom" = Table.RenameColumns( #"Removed DateTo", {{"DateFrom","Date"}}),
    #"Reordered Columns" = Table.SelectColumns( #"Renamed DateFrom", {"Date", "Currency", "Rate"}),
    #"Sorted Table" = Table.Sort(#"Reordered Columns", { {"Date", Order.Descending}, "Currency" })
in
    #"Sorted Table"

// fnMissingDates
(Table as table, ThisCurr as text, ThisDate as date) as list=>
   let 
    Source =  Table.SelectRows(Table, each [Currency] = ThisCurr and [DateTo] > Date.AddDays(ThisDate, -6) and [DateTo] < ThisDate ),
    DateFrom = Date.AddDays(List.Max(Table.Column(Source, "DateTo")), 1), 
    DateFrom2 = if DateFrom = null then ThisDate else DateFrom,
    DateList = List.Dates(DateFrom2, Int16.From(Date.AddDays(ThisDate, 1) - DateFrom2) , #duration(1, 0, 0, 0 ))
in
    DateList

Source: https://social.technet.microsoft.com/Forums/en-US/a03d68d7-9f4d-43bd-a3dd-761c5071097d/power-query-f...

I was wondering if someone could help me tweak this function. Right now it is only populating sunday and saturday, when monday is included, meaning I will not have rates on saturday or sunday before monday is loaded (often end of day on mondays). This is unfortunate as my sales are ready and I would like to report last week sales asap.

Is there a way to reconfigure the function to always populate saturdays and sundays with Friday value?

Thanks,

Alex

[v-stephen-msft]

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]

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

[Anonymous]

@Fowmy

That's a nice looking solution as well, thanks for that. However the current function I have take into consideration any blank dates and fills the last recorded rate "down". In case there is a bank holiday on a Friday it will put the rates from Thursday on Fri+Sat+Sun, which is quite nice and I would prefer to keep that functonality.

//A

[Fowmy]

@Anonymous 

I made it without creating a function, I added column and expanded, Please paste the code below and check.

let
    Source = Csv.Document(Web.Contents("https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.GBP+CAD+USD+JPY+CHF.EUR.SP00.A?startPeriod=2017-01-01", [Headers=[Accept="text/csv"]]),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"CURRENCY", "TIME_PERIOD", "OBS_VALUE"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"CURRENCY", type text}, {"TIME_PERIOD", type date}, {"OBS_VALUE", type number}}),
    #"Renamed Columns" = Table.RenameColumns( #"Changed Type", {{"CURRENCY","Currency"}, {"TIME_PERIOD", "DateTo"}, {"OBS_VALUE", "Rate"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each if Date.DayOfWeek([DateTo],Day.Monday) = 4 then List.Dates([DateTo],3,#duration(1, 0, 0, 0 )) else {[DateTo]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"DateTo"})
in
    #"Removed Columns"