Hi, I'm trying to collect historic exchange rate data from the web for a list of currencies. The starting point for the task is this:
The output I want to achieve is this:
The M Code I'm using to nearly get there is below - but I think I've misunderstood how to use 'each'. The code creates the first two columns and then I think I need to add the columns to the right sequentially and was using 'each #"Reqd Dates"[Dates to Collect]' to iterate through each required date. But I think I've misunderstood the fundamentals and the error I keep getting is Expression.Error We cannot convert a value of type Function to type Date. I've thought that by nesting two functions I could achieve my aim - but I'm stumped. Any advice or help very much appreciated
collected exchange rates v2.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
2 | tblDates: | tblBaseCurrency | tblReqdCurrencies | ||||
3 | Dates to Collect | Base Currency | Currencies | ||||
4 | 10-Jun-20 | GBP | EUR | ||||
5 | 14-Oct-19 | HRK | |||||
6 | USD | ||||||
7 | RUB | ||||||
Parameters |
The output I want to achieve is this:
collected exchange rates v2.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Currency code ▲▼ | Currency name ▲▼ | Units per GBP: 10-06-2020 | Units per GBP: 14-10-2019 | ||
2 | USD | US Dollar | 1.2757751014 | 1.177775101 | ||
3 | EUR | Euro | 1.1235103673 | 0.928510367 | ||
4 | RUB | Russian Ruble | 87.5506463020 | 87.6812463 | ||
5 | HRK | Croatian Kuna | 8.5029603632 | 8.588160363 | ||
Output |
The M Code I'm using to nearly get there is below - but I think I've misunderstood how to use 'each'. The code creates the first two columns and then I think I need to add the columns to the right sequentially and was using 'each #"Reqd Dates"[Dates to Collect]' to iterate through each required date. But I think I've misunderstood the fundamentals and the error I keep getting is Expression.Error We cannot convert a value of type Function to type Date. I've thought that by nesting two functions I could achieve my aim - but I'm stumped. Any advice or help very much appreciated
Power Query:
let
//Get Required Dates
#"Date Source" = Excel.CurrentWorkbook(){[Name="tblDates"]}[Content],
#"Reqd Dates" = Table.TransformColumnTypes(#"Date Source",{{"Dates to Collect", type date}}),
//Get Base Currency
#"Base Currency Source" = Excel.CurrentWorkbook(){[Name="tblBaseCurrency"]}[Content],
Base_Currency=#"Base Currency Source"[Base Currency]{0},
//Get Required Currencies
#"Reqd Currencies" = Excel.CurrentWorkbook(){[Name="tblReqdCurrencies"]}[Content],
reqdDate=#date(2020, 6, 9),
#"Web Data" = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=" & Base_Currency & "&date=" & Date.ToText(reqdDate,"yyyy-MM-dd") &"")){0}[Data],
#"Merged Queries" = Table.Join(#"Web Data", {"Currency code ▲▼"}, #"Reqd Currencies", {"Currencies"},JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Currencies", "Units per " & Base_Currency, Base_Currency & " per Unit"}),
GetWebData=(Base as text, aDate as date, tblCurrencies as table)=>
let
newData=Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=" & Base & "&date=" & Date.ToText(aDate,"yyyy-MM-dd") &"")){0}[Data],
SelectRows = Table.Join(newData, {"Currency code ▲▼"}, tblCurrencies, {"Currencies"}, JoinKind.Inner),
RenamedColumn = Table.RenameColumns(SelectRows, {{"Units per " & Base, "Units per " & Base & ": " & Date.ToText(aDate, "dd-MM-yyyy")}})
in
RenamedColumn,
GetColumns=(oBase as text, oDate as date, otblCurrencies as table)=>
let
#"Added Columns"=Table.AddColumn(optblCurrencies, "Units per " & oBase, GetWebData(oBase, oDate, otblCurrencies))
in
#"Added Columns",
Output = GetColumns(Base_Currency, each #"Reqd Dates"[Dates to Collect], #"Removed Columns")
in
Output