Forum Discussion

nattanarcilla's avatar
nattanarcilla
Frequent Visitor
3 years ago
Solved

Loop Multiple Website Pages based on Date

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...
  • nattanarcilla's avatar
    3 years ago

    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"