07-19-2020 06:01 PM
07-19-2020 06:01 PM
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?
07-20-2020 05:52 AMSolution
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
07-21-2020 02:51 PM
@Sergei Baklanthank 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?
07-21-2020 03:30 PM
Whoops - a little more searching and I found it. Instructions were here:
An the version of Excel where "RelativePath" fails is
and the installation where the query works is:
07-22-2020 08:52 AM
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.
07-23-2020 11:37 AM