SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1532282%22%20slang%3D%22en-US%22%3EIs%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1532282%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20working%20with%20Power%20Query%20in%20Power%20BI%20and%20recently%20tried%20to%20move%20to%20Excel%20a%20function%20that%20worked%20fine%20in%20Power%20BI.%20The%20query%20uses%20Web.Contents()%20with%20the%20parameter%20RelativePath.%20However%20when%20I%20try%20this%20in%20Excel%2C%20I%20get%20this%20error%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%22Expression.Error%3A%20Option%20'RelativePath'%20isn't%20a%20valid%20Web.Contents%20option.%20Valid%20options%20are%3A%3CBR%20%2F%3EApiKeyName%2C%20Content%2C%20ExcludedFromCacheKey%2C%20Headers%2C%20IsRetry%2C%20ManualStatusHandling%2C%20Query%2C%20Timeout%22%3C%2FP%3E%3CP%3EI%20was%20using%20RelativePath%20because%20it%20allowed%20me%20to%20avoid%20some%20difficult%20permissions%20issues%2C%20whereby%20Power%20Query%20was%20requiring%20me%20to%20enter%20credentials%20for%20every%20single%20web%20data%20source.%20That%20is%20impractical%20when%20the%20source%20names%20are%20dynamic.%20With%20RelativePath%2C%20this%20problem%20went%20away%20(in%20Power%20BI).%20But%20apparently%20the%20M%20functions%20are%20different%20in%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1532282%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533031%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F724969%22%20target%3D%22_blank%22%3E%40elkbane%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20tried%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Web.Contents(%0A%20%20%20%20%22https%3A%2F%2Fdata.gov.uk%2Fapi%22%2C%20%0A%20%20%20%20%5B%0A%20%20%20%20%20%20%20%20RelativePath%3D%223%2Faction%2Fpackage_search%22%2C%20%0A%20%20%20%20%20%20%20%20Query%3D%0A%20%20%20%20%20%20%20%20%20%20%20%20%5Bq%3D%22cows%22%2C%20rows%3D%2220%22%5D%0A%20%20%20%20%5D%0A)%0Ain%0A%20%20%20%20Source%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E(sample%20from%20Chris%20Webb)%20it%20works.%20I'm%20on%20Power%20Query%20version%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20157px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206356i24729B016FBB6170%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1537217%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1537217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3Ethank%20you%20very%20much.%20How%20can%20I%20check%20the%20version%20of%20Power%20Query%20that%20I%20have%20on%20Excel%3F%20I%20have%20two%20different%20environments%20and%20in%20one%2C%20the%20sample%20you%20provided%20works.%20I%20the%20other%2C%20it%20fails%20and%20gives%20me%20the%20same%20error%20I%20got%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpression.Error%3A%20Option%20'RelativePath'%20isn't%20a%20valid%20Web.Contents%20option.%20Valid%20options%20are%3A%3CBR%20%2F%3EApiKeyName%2C%20Content%2C%20ExcludedFromCacheKey%2C%20Headers%2C%20IsRetry%2C%20ManualStatusHandling%2C%20Query%2C%20Timeout%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20searched%20this%20forum%20and%20elsewhere%20but%20I%20can't%20find%20how%20to%20check%20my%20version%20of%20Power%20Query.%20Where%20is%20that%20accessed%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1537285%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1537285%22%20slang%3D%22en-US%22%3E%3CP%3EWhoops%20-%20a%20little%20more%20searching%20and%20I%20found%20it.%20Instructions%20were%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelguru.ca%2Fforums%2Fshowthread.php%3F7027-Update-Excel-2016-PQ-(-Get-and-Transform-)-to-Latest-Version%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelguru.ca%2Fforums%2Fshowthread.php%3F7027-Update-Excel-2016-PQ-(-Get-and-Transform-)-to-Latest-Version%3C%2FA%3E%3C%2FP%3E%3CP%3EAn%20the%20version%20of%20Excel%20where%20%22RelativePath%22%20fails%20is%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22elkbane_0-1595369584061.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207025i36095030859FB943%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22elkbane_0-1595369584061.png%22%20alt%3D%22elkbane_0-1595369584061.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20the%20installation%20where%20the%20query%20works%20is%3A%3C%2FP%3E%3CDIV%20class%3D%22modal-frame%20js-auto-size%22%3E%3CDIV%20class%3D%22modal-main%20role-hasFooter%22%3E%3CDIV%3E%3CDIV%20class%3D%22o-root-options%22%3E%3CDIV%20class%3D%22o-group-options%22%3E%3CDIV%20class%3D%22o-group-tabControl%22%3E%3CDIV%20class%3D%22o-group-tabContainers%22%3E%3CDIV%20class%3D%22o-group-diagnostics%20o-content-tab%22%3E%3CP%20class%3D%22o-content-h5%20role-versionTitle%22%3EVersion%3C%2FP%3E%3CDIV%20class%3D%22role-selectableText%20o-content-label%20role-version%22%3E2.82.5858.241%2032-bit%3C%2FDIV%3E%3CDIV%20class%3D%22role-selectableText%20o-content-label%20role-version%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22role-selectableText%20o-content-label%20role-version%22%3ESo%20it%20looks%20like%20I%20need%20to%20update%20Power%20Query.%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538688%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F724969%22%20target%3D%22_blank%22%3E%40elkbane%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20version%202.24%20is%20quite%20old.%20On%20the%20other%20hand%20as%20I%20remember%20Relative%20Path%20was%20in%20Power%20Query%20for%20years.%20Perhaps%20some%20details%20of%20syntax%20and%20parameters%20were%20changed%2C%20not%20sure.%3C%2FP%3E%0A%3CP%3EIn%20general%20yes%2C%20Power%20Query%20is%20different%20for%20different%20branches.%20Excel%20version%20is%20some%20behind%20the%20version%20for%20Power%20BI%20Desktop%2C%20and%20within%20Excel%20is%20also%20the%20difference%20-%20that's%20end%20of%20support%20Power%20Query%20add-in%2C%20PQ%20for%20Mac%20is%20limited%20version%20of%20PQ%20for%20Excel%20for%20365%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542222%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542222%22%20slang%3D%22en-US%22%3EOur%20devops%20folks%20upgraded%20that%20instance%20of%20Excel%20from%202016%20to%20O365%20(2019)%20and%20now%20I'm%20in%20business.%20My%20query%20is%20working%20in%20Excel%20as%20it%20does%20in%20Power%20BI.%20Greatly%20appreciate%20your%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544362%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20Web.Contents%20different%20in%20Excel%20Power%20Query%20than%20Power%20BI%3F%20No%20%22RelativePath%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F724969%22%20target%3D%22_blank%22%3E%40elkbane%3C%2FA%3E%26nbsp%3BGreat%2C%20glad%20to%20know%20you%20sorted%20this%20out%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

 

6 Replies
Highlighted
Best Response confirmed by elkbane (New Contributor)
Solution

@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

image.png

 

Highlighted

@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?

Highlighted

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

An the version of Excel where "RelativePath" fails is

elkbane_0-1595369584061.png

and the installation where the query works is:

Highlighted

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

Highlighted
Our devops folks upgraded that instance of Excel from 2016 to O365 (2019) and now I'm in business. My query is working in Excel as it does in Power BI. Greatly appreciate your help.
Highlighted

@elkbane Great, glad to know you sorted this out