Error - "One or more table references a dynamic Data Source ". Not able to save Dataflow

[Amar-Agnihotri]

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="&DatesFrom&"&datetill="&DatesTo&"&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"

Bold part will help you understand the issue. can anyone gives a solution for this issue. Apart from this code no other table parameter or function is used in Data flow

Thanks

[PhilipTreacy · Accepted solution]

Hi @Amar-Agnihotri 

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

[Syndicate_Admin]

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 ?

[Amar-Agnihotri]

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 ?

[Amar-Agnihotri]

@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 . 🙂

[PhilipTreacy]

Hi @Amar-Agnihotri 

Sorry I can't reproduce the issue using your code.  It works ok for me.

Regards

Phil

[Amar-Agnihotri]

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