Difficulty Building Table

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
2,172
Office Version
  1. 365
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Upvote 0
"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.
 
Upvote 0

Forum statistics

Threads
1,223,993
Messages
6,175,835
Members
452,674
Latest member
psion2600

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top