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:

[tudor2it]

This is a misleading thread, It should be only about COLUMNS.

"Select Query" parameter in "List rows present in a table" action allows us to specify which columns should be retrieved from the excel file. Here is a big confusion with the "Filter Query" that specifies the rows to be retrieved.

 @Admin please correct and separate things here. All search engines are returning this post when I'm searching for columns selection syntax. Please provide syntax examples exactly for this parameter "Select Query" as it specified in documentation. 

Typing the cvs list with column names like this Column1, Column2, 'Column 3', Column4  doesn't work.

Excel Online (Business) - Connectors | Microsoft Learn

[RajkumarS]

Step 1 : Replace the Header with "Run Script(Excel Online)" refer the code snippet below

Step 2 : use the renamed column in filter/select query

function main(workbook: ExcelScript.Workbook) {

    let HeaderCell1 = workbook.getActiveWorksheet().getRange("A1");

    let HeaderCell2 = workbook.getActiveWorksheet().getRange("B1");

    HeaderCell1.setValue("NewColumnName1");

    HeaderCell2.setValue("NewColumnName2");

}

[sakura_san]

Hello @Jcook , I have a similar issue which is about getting only the last 36 columns of the table (from the 7th column to the 43rd column) so I tried this formula  "$select": "$skip=6 to skip the 6th columns 

but my approach is not working...

The problematic I have is not having fixed column names starting the 7th column to the 43rd (they change regularly) so i need to get them by indexing (the other columns from 1st to 6th have fixed names).

I'll highly appreciate your help