Difficulty Building Table

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,916
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDE
2tblDates:tblBaseCurrencytblReqdCurrencies
3Dates to CollectBase CurrencyCurrencies
410-Jun-20GBPEUR
514-Oct-19HRK
6USD
7RUB
Parameters


The output I want to achieve is this:

collected exchange rates v2.xlsx
ABCD
1Currency code ▲▼Currency name ▲▼Units per GBP: 10-06-2020Units per GBP: 14-10-2019
2USDUS Dollar1.27577510141.177775101
3EUREuro1.12351036730.928510367
4RUBRussian Ruble87.550646302087.6812463
5HRKCroatian Kuna8.50296036328.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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,096
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.

Power Query:
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

BatCoder
Joined
Feb 28, 2002
Messages
1,096
"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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,776
Messages
5,544,160
Members
410,595
Latest member
Tatum2020
Top