Forum Discussion
Error - "One or more table references a dynamic Data Source ". Not able to save Dataflow
The issue is that Data Flows don't work with dynamic URL's, that is where you supply parameters in the actual URL. So if you use [RelativePath] and change your code to this (just one line needs to change), you should be able to save it.
Source = Web.Contents("https://www.mnb.hu/en/arfolyam-tablazat/",[RelativePath="?deviza=rbCurrencyAll&devizaSelected=ZAR&datefrom=" & DatesFrom & "&datetill=" & DatesTo & "&order=1"]),NOTE that I'm using Web.Contents
Regards
Phil
6 Replies
The issue is that Data Flows don't work with dynamic URL's, that is where you supply parameters in the actual URL. So if you use [RelativePath] and change your code to this (just one line needs to change), you should be able to save it.
Source = Web.Contents("https://www.mnb.hu/en/arfolyam-tablazat/",[RelativePath="?deviza=rbCurrencyAll&devizaSelected=ZAR&datefrom=" & DatesFrom & "&datetill=" & DatesTo & "&order=1"]),NOTE that I'm using Web.Contents
Regards
Phil
PhilipTreacy Thanks a lot . Finally it got resolved by your help. I have sent you arequest on Linkedin. It would be great if you accept that . 🙂
Hi PhilipTreacy . Now i am facing another issue . I have 3 tables. Currency Exchange Rates, USD_Rates and Currency_Symbol. Currency Exchange Rates and USD Rates are coming from the same source but Currency_Symbol is coming from a different source. Now i want to merge Currency Exchange rates and Currency Symbol. But it is showing this message -
I clicked OKay then it is asking for credentials -
Then i Logged in using anonymous because i always use this option.
Now it shows this message -
Currency_ are coming from this source -
NOW PLEASE TELL ME HOW TO RESOLVE THIS ISSUE ?
Hi @PhilipTreacy . Now i am facing another issue . I have 3 tables. Currency Exchange Rates, USD_Rates and Currency_Symbol. Currency Exchange Rates and USD Rates are coming from the same source but Currency_Symbol is coming from a different source. Now i want to merge Currency Exchange rates and Currency Symbol. But it is showing this message -
I clicked OKay then it is asking for credentials -
Then i Logged in using anonymous because i always use this option.
Now it shows this message -
Currency_ are coming from this source -
NOW PLEASE TELL ME HOW TO RESOLVE THIS ISSUE ?
Sorry I can't reproduce the issue using your code. It works ok for me.
Regards
Phil
Hi PhilipTreacy Thanks for reply. Are you able to save the above code in dataflow?
I am getting this error --
Even i edited the code in source as below to avoid parameter -
let
// DatesFrom = Text.End("0"&Text.From(Date.Day(DateTime.LocalNow())),2)&"%2F"&Text.End("0"&Text.From(Date.Month(DateTime.LocalNow())),2)&"%2F"&Text.From(Date.Year(DateTime.LocalNow())-2),
// DatesTo = Text.End("0"&Text.From(Date.Day(DateTime.LocalNow())),2)&"%2F"&Text.End("0"&Text.From(Date.Month(DateTime.LocalNow())),2)&"%2F"&Text.From(Date.Year(DateTime.LocalNow())),
Source = Web.BrowserContents("https://www.mnb.hu/en/arfolyam-tablazat?deviza=rbCurrencyAll&devizaSelected=ZAR&datefrom="&Text.End("0"&Text.From(Date.Day(DateTime.LocalNow())),2)&"%2F"&Text.End("0"&Text.From(Date.Month(DateTime.LocalNow())),2)&"%2F"&Text.From(Date.Year(DateTime.LocalNow())-2)&"&datetill="&Text.End("0"&Text.From(Date.Day(DateTime.LocalNow())),2)&"%2F"&Text.End("0"&Text.From(Date.Month(DateTime.LocalNow())),2)&"%2F"&Text.From(Date.Year(DateTime.LocalNow()))&"&order=1"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.pricetable > * > TR > :nth-child(1)"}, {"Column2", "TABLE.pricetable > * > TR > :nth-child(2)"}, {"Column3", "TABLE.pricetable > * > TR > :nth-child(3)"}, {"Column4", "TABLE.pricetable > * > TR > :nth-child(4)"}, {"Column5", "TABLE.pricetable > * > TR > :nth-child(5)"}, {"Column6", "TABLE.pricetable > * > TR > :nth-child(6)"}, {"Column7", "TABLE.pricetable > * > TR > :nth-child(7)"}, {"Column8", "TABLE.pricetable > * > TR > :nth-child(8)"}, {"Column9", "TABLE.pricetable > * > TR > :nth-child(9)"}, {"Column10", "TABLE.pricetable > * > TR > :nth-child(10)"}, {"Column11", "TABLE.pricetable > * > TR > :nth-child(11)"}, {"Column12", "TABLE.pricetable > * > TR > :nth-child(12)"}, {"Column13", "TABLE.pricetable > * > TR > :nth-child(13)"}, {"Column14", "TABLE.pricetable > * > TR > :nth-child(14)"}, {"Column15", "TABLE.pricetable > * > TR > :nth-child(15)"}, {"Column16", "TABLE.pricetable > * > TR > :nth-child(16)"}, {"Column17", "TABLE.pricetable > * > TR > :nth-child(17)"}, {"Column18", "TABLE.pricetable > * > TR > :nth-child(18)"}, {"Column19", "TABLE.pricetable > * > TR > :nth-child(19)"}, {"Column20", "TABLE.pricetable > * > TR > :nth-child(20)"}, {"Column21", "TABLE.pricetable > * > TR > :nth-child(21)"}, {"Column22", "TABLE.pricetable > * > TR > :nth-child(22)"}, {"Column23", "TABLE.pricetable > * > TR > :nth-child(23)"}, {"Column24", "TABLE.pricetable > * > TR > :nth-child(24)"}, {"Column25", "TABLE.pricetable > * > TR > :nth-child(25)"}, {"Column26", "TABLE.pricetable > * > TR > :nth-child(26)"}, {"Column27", "TABLE.pricetable > * > TR > :nth-child(27)"}, {"Column28", "TABLE.pricetable > * > TR > :nth-child(28)"}, {"Column29", "TABLE.pricetable > * > TR > :nth-child(29)"}, {"Column30", "TABLE.pricetable > * > TR > :nth-child(30)"}, {"Column31", "TABLE.pricetable > * > TR > :nth-child(31)"}, {"Column32", "TABLE.pricetable > * > TR > :nth-child(32)"}, {"Column33", "TABLE.pricetable > * > TR > :nth-child(33)"}, {"Column34", "TABLE.pricetable > * > TR > :nth-child(34)"}, {"Column35", "TABLE.pricetable > * > TR > :nth-child(35)"}, {"Column36", "TABLE.pricetable > * > TR > :nth-child(36)"}, {"Column37", "TABLE.pricetable > * > TR > :nth-child(37)"}, {"Column38", "TABLE.pricetable > * > TR > :nth-child(38)"}, {"Column39", "TABLE.pricetable > * > TR > :nth-child(39)"}, {"Column40", "TABLE.pricetable > * > TR > :nth-child(40)"}, {"Column41", "TABLE.pricetable > * > TR > :nth-child(41)"}, {"Column42", "TABLE.pricetable > * > TR > :nth-child(42)"}, {"Column43", "TABLE.pricetable > * > TR > :nth-child(43)"}, {"Column44", "TABLE.pricetable > * > TR > :nth-child(44)"}, {"Column45", "TABLE.pricetable > * > TR > :nth-child(45)"}, {"Column46", "TABLE.pricetable > * > TR > :nth-child(46)"}, {"Column47", "TABLE.pricetable > * > TR > :nth-child(47)"}, {"Column48", "TABLE.pricetable > * > TR > :nth-child(48)"}, {"Column49", "TABLE.pricetable > * > TR > :nth-child(49)"}, {"Column50", "TABLE.pricetable > * > TR > :nth-child(50)"}, {"Column51", "TABLE.pricetable > * > TR > :nth-child(51)"}, {"Column52", "TABLE.pricetable > * > TR > :nth-child(52)"}, {"Column53", "TABLE.pricetable > * > TR > :nth-child(53)"}, {"Column54", "TABLE.pricetable > * > TR > :nth-child(54)"}, {"Column55", "TABLE.pricetable > * > TR > :nth-child(55)"}, {"Column56", "TABLE.pricetable > * > TR > :nth-child(56)"}, {"Column57", "TABLE.pricetable > * > TR > :nth-child(57)"}, {"Column58", "TABLE.pricetable > * > TR > :nth-child(58)"}, {"Column59", "TABLE.pricetable > * > TR > :nth-child(59)"}, {"Column60", "TABLE.pricetable > * > TR > :nth-child(60)"}, {"Column61", "TABLE.pricetable > * > TR > :nth-child(61)"}, {"Column62", "TABLE.pricetable > * > TR > :nth-child(62)"}, {"Column63", "TABLE.pricetable > * > TR > :nth-child(63)"}, {"Column64", "TABLE.pricetable > * > TR > :nth-child(64)"}, {"Column65", "TABLE.pricetable > * > TR > :nth-child(65)"}, {"Column66", "TABLE.pricetable > * > TR > :nth-child(66)"}, {"Column67", "TABLE.pricetable > * > TR > :nth-child(67)"}, {"Column68", "TABLE.pricetable > * > TR > :nth-child(68)"}, {"Column69", "TABLE.pricetable > * > TR > :nth-child(69)"}, {"Column70", "TABLE.pricetable > * > TR > :nth-child(70)"}, {"Column71", "TABLE.pricetable > * > TR > :nth-child(71)"}, {"Column72", "TABLE.pricetable > * > TR > :nth-child(72)"}, {"Column73", "TABLE.pricetable > * > TR > :nth-child(73)"}, {"Column74", "TABLE.pricetable > * > TR > :nth-child(74)"}, {"Column75", "TABLE.pricetable > * > TR > :nth-child(75)"}, {"Column76", "TABLE.pricetable > * > TR > :nth-child(76)"}}, [RowSelector = "TABLE.pricetable > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"", type date}, {"ATS", type text}, {"AUD", type text}, {"AUP", type text}, {"BEF", type text}, {"BGL", type text}, {"BGN", type text}, {"BRL", type text}, {"CAD", type text}, {"CHF", type text}, {"CNY", type text}, {"CSD", type text}, {"CSK", type text}, {"CZK", type text}, {"DDM", type text}, {"DEM", type text}, {"DKK", type text}, {"EEK", type text}, {"EGP", type text}, {"ESP", type text}, {"EUR", type text}, {"FIM", type text}, {"FRF", type text}, {"GBP", type text}, {"GHP", type text}, {"GRD", type text}, {"HKD", type text}, {"HRK", type text}, {"HUF", type text}, {"IDR", type text}, {"IEP", type text}, {"ILS", type text}, {"INR", type text}, {"ISK", type text}, {"ITL", type text}, {"JPY", type text}, {"KPW", type text}, {"KRW", type text}, {"KWD", type text}, {"LBP", type text}, {"LTL", type text}, {"LUF", type text}, {"LVL", type text}, {"MNT", type text}, {"MXN", type text}, {"MYR", type text}, {"NLG", type text}, {"NOK", type text}, {"NZD", type text}, {"OAL", type text}, {"OBL", type text}, {"OFR", type text}, {"ORB", type text}, {"PHP", type text}, {"PKR", type text}, {"PLN", type text}, {"PTE", type text}, {"ROL", type text}, {"RON", type text}, {"RSD", type text}, {"RUB", type text}, {"SDP", type text}, {"SEK", type text}, {"SGD", type text}, {"SIT", type text}, {"SKK", type text}, {"SUR", type text}, {"THB", type text}, {"TRY", type text}, {"UAH", type text}, {"USD", type text}, {"VND", type text}, {"XEU", type text}, {"XTR", type text}, {"YUD", type text}, {"ZAR", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type", {"ATS", "AUP", "BEF", "BGL", "BGN", "CSD", "CSK", "DDM", "EEK", "EGP", "ESP", "FIM", "FRF", "GHP", "GRD", "IEP", "ITL", "KPW", "KWD", "LBP", "LTL", "LUF", "LVL", "MNT", "NLG", "OAL", "OBL", "OFR", "ORB", "PKR", "PTE", "ROL", "SDP", "SIT", "SKK", "SUR", "VND", "XEU", "XTR", "YUD"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"", "Date"}}),
#"Removed Top Rows" = Table.Skip(#"Renamed Columns", 2),
#"Unpivoted other columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {"Date"}, "Attribute", "Value"),
#"Filtered rows" = Table.SelectRows(#"Unpivoted other columns", each [Attribute] = "AUD" or [Attribute] = "BRL" or [Attribute] = "CAD" or [Attribute] = "CNY" or [Attribute] = "EUR" or [Attribute] = "GBP" or [Attribute] = "HKD" or [Attribute] = "INR" or [Attribute] = "JPY" or [Attribute] = "NZD" or [Attribute] = "RUB" or [Attribute] = "SGD" or [Attribute] = "USD")
in
#"Filtered rows"STILL I AM GETTING THE SAME ERROR
