Difficulty Building Table

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,909
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,008
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,008
"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,109,410
Messages
5,528,613
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top