Data source credentials greyed out due to dynamic source in Web.Content()

[MladenJ]

Hi,

I hope that the experts here can help me out, i'm not sure if this is possible in PBI Service or not.

What i'm trying to achieve in PBI Service is to schedule a refresh for one of the datasets, which uses custom made function that has a dynamic source.

The function is pretty straightforward, it gets the currency and finds the value in EUR which i then use to convert the needed amount.

let
    Source = (Currency as text) => let
    Source = Web.Page(Web.Contents("https://www.xe.com/", [RelativePath="currencyconverter/convert/", Query=[
             Amount="1",
             From=Currency,
             To="EUR"
         ]])),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {Currency, "EUR"}, {"Val", "EUR"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Expanded Data", {{"Val", each Text.BeforeDelimiter(_, " "), type text}, {"EUR", each Text.BeforeDelimiter(_, " "), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Val] = "1")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"EUR", type number}}),
    EUR = #"Changed Type"{0}[EUR]
in
    EUR
in
    Source

After i read blogs from Chris Web (1, 2) i have amended the Web.Contents with Relative path (as in the code above) and the refresh in PBI Desktop is working fine.

The issue is when i'm trying to refresh a dataset in PBI service, i get the error:

But, when i go over to Dataset settings, the Data source credentials is greyed out. 

Even after publishing the dataset to another workspace it's the same error. Same thing after clearing all credentials and re-entering them again.

So, i have removed the use of the function from the code, republished it, and the refresh worked when the function is not being called.

Can someone guide me to the right direction here? What am i missing?

[v-easonf-msft]

Hi,  @MladenJ 

Please check if these related threads could help.

dynamic-web-contents-and-power-bi-refresh-errors/ 

Dynamic-Web-Contents-Power-BI-Refresh-Error-using-RelativePath 

Web-Contents-with-dynamic-file-URL 

Best Regards,
Community Support Team _ Eason

[MladenJ]

Hi, thanks for replying.

I've applied few things from the mentioned posts, sorry it took some time, but unfortunately i'm unable to solve this. 

Right now, the error is different, after installing a personal gateway.

Data source privacy settings are all the same (all set to Organizational). 

The code where the mentioned function is used is as below (i have left out the other steps, as they are just for transforming the data):

let
    Source = #"BE_Global",
    tCountryInfo_Table = Source{[Item="tCountryInfo",Kind="Table"]}[Data],
    ...
    #"Added Employee cost 2 EUR" = Table.AddColumn(#"Inserted Text Between Delimiters", "Employee Cost 2 EUR", each if [Employee cost 2] = null then null else fCurrency([Currency short])*[Employee cost 2]),
    #"Added Employeer cost 2 EUR" = Table.AddColumn(#"Added Employee cost 2 EUR", "Employer cost 2 EUR", each if [Employer cost 2] = null then null else fCurrency([Currency short]) * [Employer cost 2]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Employeer cost 2 EUR",{{"Employee Cost 2 EUR", type number}, {"Employer cost 2 EUR", type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type2",{{"Employee Cost 2 EUR", each Number.Round(_, 2), type number}, {"Employer cost 2 EUR", each Number.Round(_, 2), type number}})
in
    #"Rounded Off"

Source is the Excel file (BE_Global) located on SharePoint, which is retrieved by a web connector, and i'm just referencing it in this query.

By reading this post, i guess i'm combining two different sources which are not compatible:
1. Reference from the BE_Global

2. fCurrency function (web page shown in my initial post)

How can i workaround this hiccup? 

[v-easonf-msft]

Hi,  @MladenJ 

If you still have no idea about your problem, you can open a support ticket with MSFT to seek technical support , usually they will have better suggestions.

Best Regards,
Community Support Team _ Eason