cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Syntax error ODATA Excel

I need a help. Using simple odata sheet isn't enough for flow. I'm getting a lot of syntax errors.

I need:

filter=substringof(Name, ‘urn’)


For excel ODATA I got to the point It have to be filter substringof(Name, 'Urn') and I'm getting syntax error at position 18. 

 

Thanks for help.

2 ACCEPTED SOLUTIONS

Accepted Solutions

This works as well

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

https://www.odata.org/getting-started/basic-tutorial/

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

20 REPLIES 20
abm
Super User
Super User

Hi @Anonymous 

 

Looks like your substring syntax is wrong. You need to specify startindex and length along with the string.

 

image.png

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
Anonymous
Not applicable

It's complete different function. It's ODATA. Not expression. Its not substring its substringOF. Please read more carefully 😉

Hi @Anonymous 

 

Thanks for the clarification. Yes for some reason I couldn't run substringof as FilterQuery.

 

The other alternative is use the Filter Array to filter the valueimage.png 

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

This works as well

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
abm
Super User
Super User

Here it says why it's not working

 

https://stackoverflow.com/questions/24994774/webapi-2-2-does-not-support-substringof-function

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
Anonymous
Not applicable

Can you tell me where you found contains as ODATA query? I was told to use that:

https://help.nintex.com/en-us/insight/OData/HE_CON_ODATAQueryCheatSheet.htm

 

I would be grateful for showing me where I can find it. That was pretty obvious function and I was geting mad because "Contains should be sort of basic function!" and I couldn't find anywhere proof of existing that function.

https://www.odata.org/getting-started/basic-tutorial/

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
Anonymous
Not applicable

One more. I have new issue. I need to create <contains(name,'x') or contains(name,'y')> Its possible?

I did tried now. It's not possible to have OR operator under contains. What you could do is try to set another List filter and combine both results into one.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
Anonymous
Not applicable

Thanks! From my experience filter array is bad direction. Because of performance. It's a lot of slower than using ODATA. I'm more into making parallel listing with ODATA.

Anonymous
Not applicable

One more thing. Filter array from my point of view is impossible for my flow. I'm using newly created file which I locate by dynamic element 'ID' to read rows however Power Automate don't see dynamic elements like 'ColumnName'. Then I have to parse JSON to get these elements. Maybe It will work then.

Anonymous
Not applicable

It created new problem.

Now I need solution because old way stopped working. Now I'm receiving JSON with filtered values however I need to create expression which will sum all filtered objects.

 

It's 1. filter array gives me rows of excel. 2. I need to sum these rows columns into one value.

It's 3 columns of 2 rows.

 

Do you have any idea?

Can't you iterate via loop?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
Anonymous
Not applicable

Never mind now I saw I can just use compose with add these column and add variable which is 0 on the start on the end of loop is set on sum and in second loop is added sum of columns and variable which is sum of 1st loop.

 

Thanks for all these help! You are great and I made big progress today.

Hi together,

I tried to filter as explained in this chat. Unfortunately it doesn´t work. Any idea?

Mischa2710_0-1618299025474.pngMischa2710_1-1618299038900.png

 

Hi @Mischa2710 

 

The reason is you got a space Account Number in your column name. Please see this work around. Ignore the date filter (below video) and use your column Account Number here.

 

https://www.youtube.com/watch?v=r1LhHU5lXkM

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

my problem is that I´ve only a licence for 5000 rows and my list has 20.000 rows. Therefore I´ve to reduce the number already in excel connector. Is there another way, as shown in the video?

Hi  @Mischa2710 

 

You have two options.

 

1. Rename the column name to AccontNumber. Then your original query will work.

2. If you don't want to rename then you need to extend the logic using Do Until loop.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

Thanks again. Is a questionmark or underscore in the column name also a problem for the flow?

To be honest I don´t understand your 2nd proposal with Do until.

 

I tried your 1st proposal and it works. But ... my next step is a mail and now I´m receiving 2 mails. It looks like that I receive a mail for the correct row and for the header row. Could this be?

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,894)