Syntax error ODATA Excel

[Anonymous]

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.

[abm]

Hi @Anonymous 

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

Thanks

[Anonymous]

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

[abm]

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 value 

[abm · Accepted solution]

This works as well

[abm]

Here it says why it's not working

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

[Anonymous]

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.

[abm · Accepted solution]

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

[Anonymous]

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

[abm]

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.

[Anonymous]

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]

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]

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?

[abm]

Can't you iterate via loop?

[Anonymous]

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.

[Mischa2710]

Hi together,

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

[abm]

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

[Mischa2710]

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?

[abm]

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.

[Mischa2710]

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?