Append web data in a specific table based on list of Dates

[Asamadi]

I want to write a query in power bi desktop and get data from this link:

http://www.xe.com/currencytables/?from=USD&date=2017-12-12

but my question is, how can i write a query that append new data from the link, based on the date of my windows or list of dates in a table and append to my table. 

another thing is i want it add a column that is the current date or th list of dates in a table

[v-ljerr-msft · Accepted solution]

Hi @Asamadi,

According to your description above, it seems to be that you want to get history data of a range of dates(i.e. from 2017-12-01 to 2017-12-12) from that web site, right?

If that is the case, you can firstly create a function named "GetData" to get data of a specific date from that site.

(date1 as date) as table =>
let
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date="&Date.ToText(date1,"yyyy-MM-dd"))),
    Data = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})
in
    #"Changed Type"

Then use the formula below to create a new query to get all data from the date range which you can specify in the query.

let
    Source = List.Dates(#date(2017, 12, 1), 10, #duration(1, 0, 0, 0)),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Renamed = Table.RenameColumns(ToTable,{{"Column1", "Date"}}),
    Added = Table.AddColumn(Renamed, "Custom", each GetData([Date])),
    #"Expanded Custom" = Table.ExpandTableColumn(Added, "Custom", {"Currency code ▲▼", "Currency name ▲▼", "Units per USD", "USD per Unit"}, {"Custom.Currency code ▲▼", "Custom.Currency name ▲▼", "Custom.Units per USD", "Custom.USD per Unit"})
in
    #"Expanded Custom"

Here is the sample pbix file for your reference. 

Regards