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

[Anonymous]

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!

[efialttes]

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

[Anonymous]

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]

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

[Anonymous]

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

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"

}

[efialttes]

@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

[Jcook · Accepted solution]

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: