Difficulty Building Table

[pjmorris]

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:

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

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

 

[smozgur]

I didn't work with Table.AddColumn function yet, and I am not sure how to match the corresponding currencies by using this function, what if the returned data is not in the same order. However, it is on my study list.

Until then, I am a big fan of List.Generate function. I know it is an expensive function but should be ok for small data to iterate. In case you are interested, here how I would get the necessary data.
(I also don't like using constants in M code and try to avoid as much as I could. If the column order doesn't change in the source table, then it should work as expected. And I also prefer writing code separated into lines. So it might look a bit longer).

Basically, I add each rate table for requested dates by using the join in the loop. Then I use the last list element which is a formed table.

let
    fnGetWebData = (from as text, i as number) as table =>
        let 
            ratesDate = Date.ToText(Date.From(DateTable[Dates to Collect]{i}), "yyyy-MM-dd"),
            HtmlData = Web.Contents("https://www.xe.com/currencytables/?from=" & from & "&date=" & ratesDate),
            WebPage = Web.Page(HtmlData),

            RateTable = WebPage{0}[Data],
            RenameColumn = Table.RenameColumns(
                                RateTable, 
                                {
                                    Table.ColumnNames(RateTable){2}, 
                                    Table.ColumnNames(RateTable){2} & ": " & Date.ToText(Date.From(DateTable[Dates to Collect]{i}), "dd-MM-yyyy")
                                }
                            ),
            Result = Table.SelectRows(
                        RenameColumn, 
                        each List.Contains(
                                ReqCurrTable[Currencies], 
                                Record.Field(_, Table.ColumnNames(RateTable){0})
                            )
                        )    
        in
            Result,

    DateTable = Excel.CurrentWorkbook(){[Name="tblDates"]}[Content],
    BaseCurrTable = Excel.CurrentWorkbook(){[Name="tblBaseCurrency"]}[Content],
    ReqCurrTable = Excel.CurrentWorkbook(){[Name="tblReqdCurrencies"]}[Content],

    RateList = List.Generate(
        () => [i = 1, MainTable = fnGetWebData(
                                    BaseCurrTable[Base Currency]{0}, 
                                    0
                                )],
        each [i] <= Table.RowCount(DateTable),
        each [i = [i] + 1, MainTable = 
                let
                    RateTable = fnGetWebData(
                            BaseCurrTable[Base Currency]{0}, 
                            [i]
                        ),
                    JoinTable = Table.NestedJoin(
                        [MainTable],
                        Table.ColumnNames([MainTable]){0},
                        RateTable,
                        Table.ColumnNames(RateTable){0},
                        "NewDate",
                        JoinKind.Inner
                    ),
                    Result = Table.ExpandTableColumn(
                        JoinTable,
                        "NewDate",
                        {Table.ColumnNames(RateTable){2}}
                    )
                in 
                    Result
        ]
    ),
    
    RateTable = List.Last(RateList)[MainTable],
    ColumnRemoved = Table.RemoveColumns(RateTable,{Table.ColumnNames(RateTable){3}}),

    Result = Table.TransformColumnTypes(
                ColumnRemoved, 
                List.Transform(
                    List.LastN(Table.ColumnNames(ColumnRemoved), Table.ColumnCount(ColumnRemoved) - 2), 
                    each {_, type number}
                )
            )
in
    Result

 

[smozgur]

"each" will work with the functions that provide a list as a data source so it can do the job on "each" item.
That's why I created a loop in my code by using List.Generate to call the custom function, fnGetWebData, for each date and also form a new table by using that data. I know we are not using the same logic/functions but you can get the loop idea from the code.

And I recently wrote an article about it. Perhaps it might give an idea as well.

Power Query List.Generate vs. VBA For...Next

When I read Bill's "How Would You Clean This Data" challenge couple months ago, I thought that it could be easily done by using VBA. However, the article was all about Power Query. I was interested in learning the M language. I was even more...

www.mrexcel.com