Web.content error when combining queries

[Arendp]

I have a CSV where I define URL to get data from.

When I create a connection to the CSV, I can easily get this URL:

let
Bron = Csv.Document(Web.Contents("****GoogleSheetLink****"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    URLAuth = Bron{1}[Column2]
in 
URLAuth

Then I want to use this URL to get the data I need:

let
URLAuth = URLToUse,
AccessKeyParam = AccessKey,
SourceAccessKey = Xml.Tables(Web.Contents(URLAuth, [Query=[accessKey=Text.From(AccessKeyParam)]]))
in
    SourceAccessKey

Both individually work fine, also in PowerBI online (with scheduled refresh).

However, when I combine these into:

let
Bron = Csv.Document(Web.Contents("****GoogleSheetLink****"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    URLAuth = Bron{1}[Column2],
AccessKeyParam = AccessKey,
SourceAccessKey = Xml.Tables(Web.Contents(URLAuth, [Query=[accessKey=Text.From(AccessKeyParam)]]))
in
    SourceAccessKey

I run into this issue in PowerBI online:

You can't do a planned refresh for this dataset because the following dataset doesn't support refresh (translated from Dutch 🙂 )
with error:

Query contains unsupported function. Function name: Web.Contents

Does somebody know how both individually work,but combined show above error in PowerBI online.

Everything works fine in Desktop.

[v-juanli-msft]

Hi @Arendp 

Please refer to:

Web API Data Sources with Power Query and Scheduling Data Refresh in the Power BI Service

https://community.powerbi.com/t5/Service/Refresh-error-when-combining-multiple-Web-queries/td-p/2670...

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Arendp]

I have built the queries like explained here.

And then split the queries like explained here.

Funny enough, when I did the splitting it gave me the Formula.Firewall error. Before that, it's wasn't showing me this.

I have checked all Privacy settings to make sure they are aligned. This didn't solve my problem.

Am a bit lost now.

So, my first query now consists of:

let

Source = #"Google Sheet API URLs",
URLPre = Text.Replace(Source,"https://docs.google.com/",""),

SourceCSV = Csv.Document(Web.Contents("https://docs.google.com", [RelativePath = URLPre])),

URLAuth = SourceCSV{1}[Column2],
#"Geconverteerd naar tabel" = #table(1, {{URLAuth}}),

in
   #"Geconverteerd naar tabel"

And the second one:

let

URLAuth = Text.From(#"Auth URL"),
AccessKeyParam = AccessKey,

BronAccessKey = Xml.Tables(Web.Contents(URLAuth, [Query=[accessKey=Text.From(AccessKeyParam)]])),
  
 in
    BronAccessKey

[Arendp]

I got a bit further. I made a function from both calls like suggested in some of the links.

So this is the function which calls the Auth Code, with the Auth URL as input:

let LoadAuthCode = (URLAuth as text) =>

    let

    AccessKeyParam = YukiAccessKey,

    BronAccessKey = Xml.Tables(Web.Contents(URLAuth, [Query=[accessKey=Text.From(AccessKeyParam)]])),
    
    in
        BronAccessKey

in 
    LoadAuthCode

URLauth I get from this function:

let
    Bron = () => let
    
    Source = "**GoogleSheetLink**",
    
    URLPre = Text.Replace(Source,"https://docs.google.com/",""),
    
    SourceCSV = Csv.Document(Web.Contents("https://docs.google.com", [RelativePath = URLPre])),
        Column1 = SourceCSV{1}[Column2],
        #"Geconverteerd naar tabel" = #table(1, {{Column1}})
    in
        #"Geconverteerd naar tabel"
in
    Bron

Both functions work, also in PBI online environment.

Troubles start now;

I call the 2nd function and add a column where I call the 1st function with the URL gotten in 2nd function as input:

let
    Bron = FunctieAuthURL(),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(Bron, "Aangepast", each FunctionAuthCode([Column1]))
in
    #"Aangepaste kolom toegevoegd"

It give me a result, but in PBI Online I run into this error:

Query contains unsupported function. Function name: Web.Contents

It's strange, because I call both data sources (AuthURL and AuthCode) in 2 different datasources, refer to them in 2 different functions. I combine them in a third function. 

How come this doesn't work?

[aTChris]

@Arendp 

You might find it's a security level issue. Try combining the functions and query into a single source.

Not tested this but fingers crossed.

let
    
FunctieAuthURL =

let
    Bron = () => let
    
    Source = "**GoogleSheetLink**",
    
    URLPre = Text.Replace(Source,"https://docs.google.com/",""),
    
    SourceCSV = Csv.Document(Web.Contents("https://docs.google.com", [RelativePath = URLPre])),
        Column1 = SourceCSV{1}[Column2],
        #"Geconverteerd naar tabel" = #table(1, {{Column1}})
    in
        #"Geconverteerd naar tabel"
in
    Bron,

FunctionAuthCode = 

let LoadAuthCode = (URLAuth as text) =>

    let

    AccessKeyParam = YukiAccessKey,
    BronAccessKey = Xml.Tables(Web.Contents(URLAuth, [Query=[accessKey=Text.From(AccessKeyParam)]]))
    
    in
        BronAccessKey

in 
    LoadAuthCode,

    Bron = FunctieAuthURL(),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(Bron, "Aangepast", each FunctionAuthCode([Column1]))
in
    #"Aangepaste kolom toegevoegd"

[Arendp]

@aTChris 

Works good in Desktop!

Made a minor change, added the AccessKey to function FunctionAuthCode()

Still I get the message Query contains unsupported function. Function name: Web.Contents

In the online environment...

let
    
FunctieAuthURL =

        let
            Bron = () => 
            
                let
                
                    Source = "***GoogleSheetLink***",
                    
                    URLPre = Text.Replace(Source,"https://docs.google.com/",""),
                    
                    SourceCSV = Csv.Document(Web.Contents("https://docs.google.com", [RelativePath = URLPre])),
                    Column1 = SourceCSV{1}[Column2],
                    #"Geconverteerd naar tabel" = #table(1, {{Column1}})
                in
                    #"Geconverteerd naar tabel"
        in
            Bron,

FunctionAuthCode = 

        let LoadAuthCode = (URLAuth as text, YukiAccessKey as text) =>

                    let
                        BronAccessKey = Xml.Tables(Web.Contents(URLAuth, [Query=[accessKey=YukiAccessKey]]))
                    
                    in
                        BronAccessKey

        in 
            LoadAuthCode,

            Bron = FunctieAuthURL(),
            #"Aangepaste kolom toegevoegd" = Table.AddColumn(Bron, "Aangepast", each FunctionAuthCode([Column1], Text.From("***AccessKey***"))),
            #"Aangepast uitgevouwen" = Table.ExpandTableColumn(#"Aangepaste kolom toegevoegd", "Aangepast", {"Element:Text"}, {"Aangepast.Element:Text"})
in
    #"Aangepast uitgevouwen"

As mentioned, all is on Anonymous / Public.

[aTChris]

@Arendp 

I find it strange that the functions in the cloud separately. I assume that the issue is with the Web.Contents(URL.Auth,

The only solution im aware of is currently disabled. PowerBI Cloud has a feature to skip the test but its currently disabled.

PBI Skip Test Disabled

Hope they enable it again soon for you. 

[SteveCampbell]

It's because you are using a variable as a URL. If these are all google sheet then i assume they all have the base URL of: https://docs.google.com/

you can try removing this from the URL first.

URLPre = Table.ReplaceValue(BRON,"https://docs.google.com/","",Replacer.ReplaceText,{"Column2"})

In web.contents you just the url as  "https://docs.google.com/". Then you append the rest using RelativePath

SourceAccessKey = Xml.Tables(Web.Contents("https://docs.google.com/", [RelativePath = URLAuth, Query=[accessKey=Text.From(AccessKeyParam)]]))

  let
Bron = Csv.Document(Web.Contents("****GoogleSheetLink****"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),

URLPre = Table.ReplaceValue(BRON,"https://docs.google.com/","",Replacer.ReplaceText,{"Column2"})

URLAuth = URLPre{1}[Column2],
AccessKeyParam = AccessKey,
SourceAccessKey = Xml.Tables(Web.Contents(URLAuth, [RelativePath = "https://docs.google.com/", Query=[accessKey=Text.From(AccessKeyParam)]]))
  in
SourceAccessKey
	

In the service when you add to gateway, you can check "Skip connection test". This is checking if https://docs.google.com/ is a valid URRL. In this case it is, but for others reading this solution may not be. 

[Arendp]

Thanks for your answer @SteveCampbell 

So both URLs have now the Web.Content including the RelativePath.

Still both URLs individually work, but when I combine them (becasuse first one delivers input for the second one), the refresh in online environment fails.

As said, in Desktop it works fine.

So somehow in the refresh process, both queries bite each other.