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=",...
Need Help
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?

      • aTChris's avatar
        aTChris
        Resolver I
        6 years ago

        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"