cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Super User
Super User

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

Hello @jnosko,

 

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!





View solution in original post

6 REPLIES 6
Highlighted
Dual Super User
Dual Super User

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

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!



Highlighted
Frequent Visitor

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

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"
}
Highlighted
Super User
Super User

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

Hi @jnosko 

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!





Highlighted
Frequent Visitor

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

@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"
}
 
 
Highlighted
Dual Super User
Dual Super User

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

@jnosko
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-N...

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!



Highlighted
Super User
Super User

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

Hello @jnosko,

 

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!





View solution in original post

Helpful resources

Announcements
firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (7,089)