cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Common Expression Error stumping me for concatenating url

Here is the error: 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

 

Here is the syntax I am trying to use to create a URL to retrieve data from a url when I add a custom column

 

= Json.Document(Web.Contents("https://xxxxxx.xxx-api.xxxxx-xxxx-x.xxxxxxxxxxxxxx.xxxx/env/app?id=" & Text.From([Column1.ApplicationID]), [Headers=[#"xxxxx"="xxxxxxxxxxxxxxxxxx"]])) 

 

The bolded part is the nasty bits.  I have tried several different versions of that but am not having much luck.  Not sure why, I am new to PowerQuery.  Not even sure if this is how it's done.  It certainly is easier to do something like this in Google Sheets with the json functions there.  It would be nice if WEBSERVICE in excel would just work given an https.  It seems dirty to have to run 1 query to get a list of all the IDs I need then have to run 1000+ queries to get the detail but that's how the API is designed for the external system I am working with.  I don't understand why I can't just directly reference a column in the spreadsheet I am working with that has this URL already concatenated.  I am probably not doing this the right way, just trying to hack it out.  I can hit the connection just fine and pull all the IDs I need, its just drilling down to the next level is proving difficult. 

 

I also get this error, when I try and use an _ 

Expression.Error: The name '_' wasn't recognized. Make sure it's spelled correctly.

= Json.Document(Web.Contents("https://xxxxxxxxxx.xxxxx/dev/app?id=" & _[Column1.ApplicationID], [Headers=[#"xxx"="xxxxxxxx"]]))

 

This just isn't clicking for me.  Any help is greatly appreciated

 

 

 

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

Your syntax look correct. Just to confirm you are using this in a custom column in a table that has a column called Column1.ApplicationID.

 

If so, to troubleshoot you can try to hard code in one of your ID values like below to narrow it down to the Text.From part.  You could separately make a custom column with Text.From(... to confirm you are pulling the ID value as text correctly.

 

= Json.Document(Web.Contents("https://xxxxxx.xxx-api.xxxxx-xxxx-x.xxxxxxxxxxxxxx.xxxx/env/app?id=" & "10", [Headers=[#"xxxxx"="xxxxxxxxxxxxxxxxxx"]])) 

 

Pat

 

Syndicate_Admin
Administrator
Administrator

Hi @va_mike512 ,

The grammar about 

Json.Document(Web.Contents("https://xxxxxx.xxx-api.xxxxx-xxxx-x.xxxxxxxxxxxxxx.xxxx/env/app?id=" & Text.From([Column1.ApplicationID]), [Headers=[#"xxxxx"="xxxxxxxxxxxxxxxxxx"]])) 

seems ok, if you want to creae a new column to quote them dynamically, you can create a custom function like this and invoke this function for your ID column:

1.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,394)