Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |