Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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:
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.
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:
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.