Forum Discussion

elkbane's avatar
elkbane
Copper Contributor
Jul 20, 2020
Solved

Is Web.Contents different in Excel Power Query than Power BI? No "RelativePath"

I've been working with Power Query in Power BI and recently tried to move to Excel a function that worked fine in Power BI. The query uses Web.Contents() with the parameter RelativePath. However when I try this in Excel, I get this error:

"Expression.Error: Option 'RelativePath' isn't a valid Web.Contents option. Valid options are:
ApiKeyName, Content, ExcludedFromCacheKey, Headers, IsRetry, ManualStatusHandling, Query, Timeout"

I was using RelativePath because it allowed me to avoid some difficult permissions issues, whereby Power Query was requiring me to enter credentials for every single web data source. That is impractical when the source names are dynamic. With RelativePath, this problem went away (in Power BI). But apparently the M functions are different in Excel?

 

excel
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 20, 2020

    elkbane 

    Just tried

    let
    Source = Web.Contents(
    "https://data.gov.uk/api", 
    [
        RelativePath="3/action/package_search", 
        Query=
            [q="cows", rows="20"]
    ]
)
in
    Source

    (sample from Chris Webb) it works. I'm on Power Query version

     

6 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 20, 2020

    elkbane 

    Just tried

    let
    Source = Web.Contents(
    "https://data.gov.uk/api", 
    [
        RelativePath="3/action/package_search", 
        Query=
            [q="cows", rows="20"]
    ]
)
in
    Source

    (sample from Chris Webb) it works. I'm on Power Query version

     

    • elkbane's avatar
      elkbane
      Copper Contributor
      Jul 21, 2020

      SergeiBaklanthank you very much. How can I check the version of Power Query that I have on Excel? I have two different environments and in one, the sample you provided works. I the other, it fails and gives me the same error I got before.

       

      Expression.Error: Option 'RelativePath' isn't a valid Web.Contents option. Valid options are:
      ApiKeyName, Content, ExcludedFromCacheKey, Headers, IsRetry, ManualStatusHandling, Query, Timeout

       

      I've searched this forum and elsewhere but I can't find how to check my version of Power Query. Where is that accessed?

      • elkbane's avatar
        elkbane
        Copper Contributor
        Jul 21, 2020

        Whoops - a little more searching and I found it. Instructions were here:

        https://www.excelguru.ca/forums/showthread.php?7027-Update-Excel-2016-PQ-(-Get-and-Transform-)-to-Latest-Version

        An the version of Excel where "RelativePath" fails is

        and the installation where the query works is:

        Version

        2.82.5858.241 32-bit
         
        So it looks like I need to update Power Query.

Resources