cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnosko
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
Jcook
Super User III
Super User III

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!


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





View solution in original post

6 REPLIES 6
efialttes
Super User III
Super User III

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!



jnosko
Frequent Visitor

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

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!


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





jnosko
Frequent Visitor

@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"
}
 
 

@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!



Jcook
Super User III
Super User III

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!


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





View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,233)