Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nattanarcilla
Frequent Visitor

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"

 

 

 

 

 

1 ACCEPTED SOLUTION
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"

View solution in original post

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors