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 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"

 

 

 

 

 

  • 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"

1 Reply

  • nattanarcilla's avatar
    nattanarcilla
    Frequent Visitor

    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"