OData syntax for sharepoint column with a space - Get Items action.

[martinav]

I really am not thrilled that I have to learn an entirely different language to do filters in Flow.  But, that is what we have to live with, so I have to bother everyone with how to do this.

My Sharepoint column has a space in it.  How do you format the filter statement with a space in a column name.  I have tried these methods without success.

Part Number eq DVPartNumber

'Part Number' eq DVPartNumber

Part_x0020_Number eq DVPartNumber

Its hard to believe there are yet other ways to do this.  I need to know the right way.

[pva_gg210]

My suggestion would be to use the Get Items action, run it, and determine how the name of the column appears in the Output result. Sometimes it doesn't match what you see on Lists. 

[lashotoSP]

@jblackcitrin 

The same thing happened to me. Turned out my columns were named something arbitrary like Column1. Not sure why that happened, but the video that I posted on this has Reza Dorrani explaining this in a pretty simple way.

Basically, if you go to settings and turn on experimental features Power Automate will return all your columns and give you a much simpler interface. It it worth the watch.

[lottidah]

If I could send you a cup of coffee for this, I would!  Thank you!!!  I have been pulling my hair out because nothing was working with my field called "Follow Up."  The URL showed this field should be referenced as Follow_x0020_Up_x0020_Date - I would have never known if it weren't for your tip!!

[Clifferton]

I'm using online sharepoint exclusively, and I got failures when replacing space with '_x0020_'. My column displays as "Gate Count" but the internal sharepoint name for the column is "GateCount" which seems to be the newest way this works. It's a shame this very old community post is listed first in the google results for my query.

[martinav]

@Clifferton 

Unfortunately, there is no context with PA revisions on posts.  Just makes more to sort through.  However, your additional knowledge, and updating the post is what brings them up to date.  So, it is a service to the community to share  your results.

[pva_gg210]

List rows present in a table Filter Query doesn't support spaces however there is a workaround that WILL work with spaces! After your list rows action, insert a filter array action "From" the value of the list rows, then you can filter for your specified criteria. Once you do that you can reference the outputs from the filter array action in the following steps. I've attached an example.

edit: I just realized this is related to sharepoint filter, not excel, but this tip is for excel 🙂

[Clifferton]

Best process I've found when working with unfamiliar sharepoint columns is to put a Get Items action with settings for the list in question. Then run the flow. 

If you then go into the flow results and download the output from the Get Items action, you can see what your columns are named internally. For example, you can see my "Gate Count" and "Entered By" columns are named 'GateCount' and 'EnteredBy' respectively.

[pva_gg210]

If you enable "Experimental Features" by going to the gear icon > view all power automate settings > turning it on and saving, you'll see the filter query has been updated to allow you to select the column as you see it on the front end, not what the system sees in the backend 🙂 My previous reply was addressing Excel filter query which I now realize wasn't the topic of the thread.

[Nicer_Atl]

Can't get this to work in a flow that is doing the "List rows present in a table" action from an Excel file.  Have tried all the syntax mentioned here, but still get "invalid filter clause: cannot find 'The Column Name' column." error. 

I'm doing this in a Flow within Power Virtual Agents.  Does this make a difference?  Any help appreciated.  Where do you find the experimental features in Virtual Agents Flows?

[elc123]

Just jumping on to say thanks for this thread and solution! I have a html POST that's taking input from a ms form, and posting the content into a new page on a sharepoint site.

With a bit of testing, I determined that the flow worked fine when I put test content in the form with no spaces - aaaaaaaaaaaaaaaaaa for example. But when I put aaaaaaa bbbbbb cccccccccc it failed with a bad gateway error.

This led me to this thread as I was able to search properly for the source of the error!

I have now wrapped the dynamic values in my json in single quotes, and it works! Oddly, the single quotes do get posted to the sharepoint page. Which I guess I can live with until I find some other clever solution.

[RustyPole]

I have exactly the same issue. Did you find the solution?