Forum Discussion

Arendp's avatar
Arendp
Helper III
6 years ago

Web.content error when combining queries

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.

Need Help

8 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    6 years ago

    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's avatar
      Arendp
      Helper III
      6 years ago

      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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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/26709

    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's avatar
      Arendp
      Helper III
      6 years ago

      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's avatar
        Arendp
        Helper III
        6 years ago

        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?