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
Fowmy's avatar
Fowmy
Super User
5 years ago

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"