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...
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

     

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.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 22, 2020

        elkbane 

        Yes, version 2.24 is quite old. On the other hand as I remember Relative Path was in Power Query for years. Perhaps some details of syntax and parameters were changed, not sure.

        In general yes, Power Query is different for different branches. Excel version is some behind the version for Power BI Desktop, and within Excel is also the difference - that's end of support Power Query add-in, PQ for Mac is limited version of PQ for Excel for 365, etc.

Resources