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

Select Query syntax for Excel "List rows present in table"

I'm working with the Excel List Rows Present In Table action.  I was trying to select just certain columns using the Select Query advanced option.  What is the syntax to use if my column has spaces?  I've tried underscores, double/single quotes, _x0200, tick marks, square brackets.  I know the best option is to have no spaces in the name but at this point I can't change that.   Also, would it  just be better to use a Filter Array and/or Select action instead of fiddling with this Select Query option?  Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Jcook
Super User
Super User

Hello @Anonymous,

 

As @efialttes had mentioned, it looks like this feature to use select query inside excel with a column with a space is not yet supported.

 

However, you can try and use the Select action. Here is my example:

Excel Space in Column.png

 


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

6 REPLIES 6
efialttes
Super User
Super User

Hi!
My suggesrion is, remove temporarily your Filter query expression, execute the flow, and inspect 'List rows present in a table' outputs to identify internal column name, since spaces are special characters
Hooe this helos


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

Yeah, that was the first thing I did and it didn't work and that's why I started researching.  

I get this error message for a column name Screening Date.  

{
  "status"400,
  "message""Term 'Screening Date' is not valid in a $select or $expand expression.\r\n     inner exception: Term 'Screening Date' is not valid in a $select or $expand expression.\r\nclientRequestId: 627cffe2-a119-4031-bf57-d80044a767e1",
  "error": {
    "message""Term 'Screening Date' is not valid in a $select or $expand expression.\r\n     inner exception: Term 'Screening Date' is not valid in a $select or $expand expression."
  },
  "source""excelonline-eus2.azconn-eus2.p.azurewebsites.net"
}

Hi @Anonymous 

Could you share a picture of your flow?

 

I seen in your original post, you had mentioned you tried _x0200 

Can you try:

_x0020_

 

Also as @efialttes had mentioned can you remove the filters, as well as the expand. Just do a List rows with no additional settings


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





Anonymous
Not applicable

@Jcook 

oops, I did mistype that in my original post, it was _x0020_ that I tried for a space. 🙂  And here are my results testing empty filters and also column names with only 1 word...

 

So here is a screenshot of the action I'm using:

Capture.PNG

 

And the output (I've only include the first record here) when I leave the filters empty looks like this:

 

{"body":{"value":[{"@odata.etag":"","ItemInternalId":"6d269a8b-a6eb-4c26-80d5-c2d530517d31","ID":"163","Start time":"43866.7106481481","Completion time":"43866.7122685185","Name":"Bonnie","Outside CLIA Slide Total":"","Screening Date":"43831","Non-Gyn Smears, Cell Blocks":"","Non-Gyn Thinprep, Surepath, Cytospin":"","Gyn Thinprep FOV Only":"","Gyn Thinprep FOV + Manual":"","Gyn Thinprep Manual Only":"","Gyn Surepath FOV Only":"","Gyn Surepath FOV + Manual":"","Gyn Surepath Manual Only":"","QC Screened":"","Proficiency Testing, Educational Slides, QA Slides":"","Screening Hours":"0","Outside Screening Hours":"","Total non-screening hours":"8","Brief description of non-screening activities":"New Year's Day","Did you screen at other labs today?":"No","Total UCL CLIA Slides":"0","Total UCL+Outside Scr Hours":"0","UCL Sl/Hr":"**No screening recorded for UCL today.","Total UCL+Outside CLIA Slides":"0","UCL+Outside Sl/Hr":"**No screening recorded today.","Total UCL Hours Worked":"8","slide rate over":"","duplicate scr date":"","slide count over":"","scr hours over":"","Column1":"2020-02-05","sup alert":"",

 As an example, I have columns called Name, Column1, Screening Date.  If I test entering one word column names separated by commas into the select query I get the correct output for those columns. But if I try to enter a column into it with 2 words like Screening Date I get a BadRequest error (see below.)  So I've tried every way to format the name I can think to use it, the quotes and brackets etc.  I just haven't stumbled upon the correct one for it yet. 

 

So, when I select query for name,column1 I get this:

{
  "value": [
    {
      "@odata.etag""",
      "ItemInternalId""0adb76d7-cc84-4f8e-9e41-4b812a27fd85",
      "Name""Bonnie",
      "Column1""2020-02-05"
    },
   
 
And if I select query for name,screening date (or even just screening date by itself), I get this:
{
  "status"400,
  "message""Term 'name,screening date' is not valid in a $select or $expand expression.\r\n     inner exception: Term 'name,screening date' is not valid in a $select or $expand expression.\r\nclientRequestId: 575cefd1-4784-4be9-95ce-51459e2b45f1",
  "error": {
    "message""Term 'name,screening date' is not valid in a $select or $expand expression.\r\n     inner exception: Term 'name,screening date' is not valid in a $select or $expand expression."
  },
  "source""excelonline-eus2.azconn-eus2.p.azurewebsites.net"
}
 
 

@Anonymous
According to this post from oct last year this feature was not supported

https://powerusers.microsoft.com/t5/Building-Flows/Simple-Question-Filter-Query-Where-Excel-Column-Name-Has-a-Space/td-p/382231

Sorry for the bad news


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Jcook
Super User
Super User

Hello @Anonymous,

 

As @efialttes had mentioned, it looks like this feature to use select query inside excel with a column with a space is not yet supported.

 

However, you can try and use the Select action. Here is my example:

Excel Space in Column.png

 


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

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (3,004)