Forum Discussion
nattanarcilla
3 years agoFrequent 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...
- 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"
nattanarcilla
3 years agoFrequent 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"