Loop Multiple Website Pages based on Date

[nattanarcilla]

I'm trying to loop between multiple website pages. What I want to do is to invoke CreateFxTable so the values will change based on the corresponding date generated by CreateDateTable. I can't seem to make it work though.

let CreateFxTable = (Date as text, MainCurrency as text) as table =>
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>

    let
        DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
        Source2 = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
        TableFromList = Table.FromList(Source2, Splitter.SplitByNothing()),

        Source = Web.BrowserContents("https://www.website.com/currencytables/?from="&MainCurrency&"&date="&Date),
        #"Extracted Table From Html" = Html.Table(Source, {{"Currency", "TABLE.currencytables__Table-xlq26m-3.jaGdii > TBODY > TR > :nth-child(1)"}, {"USD Per Unit", "TABLE.currencytables__Table-xlq26m-3.jaGdii > TBODY > TR > :nth-child(4)"}}, [RowSelector="TABLE.currencytables__Table-xlq26m-3.jaGdii > TBODY > TR"]),
        #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Currency", type text}, {"USD Per Unit", type number}}),
        #"Filter"= Table.SelectRows(#"Changed Type", each List.Contains( {"USD","AUD","CAD","CHF","EUR","GBP","INR","MXN","PHP","PEN","PLN","RMB","SGD","HKD"} , [Currency] ))
    
    in
        TableFromList,
        InvokedCreateDateTable = CreateDateTable(#date(Date.Year(DateTime.LocalNow())-9,4,1), #date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),Date.Day(DateTime.LocalNow())), null),
        ChangedType = Table.TransformColumnTypes(InvokedCreateDateTable,{{"Column1", type date}}),
        FormatDatetoText = Table.AddColumn(ChangedType, "Date", each Date.ToText([Column1],[Format="yyyy-MM-dd"])),
        RemoveOriginalDateColumn = Table.RemoveColumns(FormatDatetoText,{"Column1"}),
        FilteredRows = Table.SelectRows(RemoveOriginalDateColumn, each ([Date] = "2013-04-01" or [Date] = "2013-04-02"))
    in
    #"Filter",
    #"Invoked Custom Function" = Table.AddColumn(FilteredRows, "Exchange Rates (2)", each CreateFxTable([Date], "USD"))
in
#"Invoked Custom Function"

[nattanarcilla · Accepted solution]

Solve it with below revised query

let
        //Start of DateTable
        StartDate = #date( Date.Year(DateTime.LocalNow())-9, 1, 1),
        EndDate = #date( Date.Year(DateTime.LocalNow()), Date.Month(DateTime.LocalNow()), Date.Day(DateTime.LocalNow())),
        Source = List.Dates(
                StartDate,
                Duration.Days( EndDate - StartDate ) + 1,
                #duration( 1, 0, 0, 0 )
        ),
        DateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"OldDate"}, null, ExtraValues.Error),
        FormatDatetoText = Table.AddColumn(DateListToTable, "Date", each Date.ToText([OldDate],[Format="yyyy-MM-dd"])),
        DateTable = Table.RemoveColumns(FormatDatetoText,{"OldDate"}),

        AddColumns = Table.AddColumn(DateTable,"Rate", each
                let
                     WebSource = Web.BrowserContents("https://www.website.com/currencytables/?from=USD&date="& [Date]),
                     ExtractFromWeb = Html.Table(WebSource, {{"Currency", "TABLE.currencytables__Table-xlq26m-3.jaGdii > TBODY > TR > :nth-child(1)"}, {"USD Per Unit", "TABLE.currencytables__Table-xlq26m-3.jaGdii > TBODY > TR > :nth-child(4)"}}, [RowSelector="TABLE.currencytables__Table-xlq26m-3.jaGdii > TBODY > TR"]),
                         ChangeType = Table.TransformColumnTypes(ExtractFromWeb,{{"Currency", type text}, {"USD Per Unit", type number}}),
                     FilterCurrency = Table.SelectRows(ChangeType, each List.Contains( {"USD","AUD","CAD","CHF","EUR","GBP","INR","MXN","PHP","PEN","PLN","RMB","SGD","HKD"} , [Currency] )) // Can add currency here
                in
                     FilterCurrency
        ),
    #"Expanded Rate" = Table.ExpandTableColumn(AddColumns, "Rate", {"Currency", "USD Per Unit"}, {"Rate.Currency", "Rate.USD Per Unit"})

in
#"Expanded Rate"