Simple Question: Filter Query Where Excel Column Name Has a Space

[Anonymous]

I can get the following filter query to work (shown with space removed) UNLESS I have a space in the Excel column name (e.g., Remediation Responsibility). I have tried both single and double quotes and neither works. Adding "_x0020_" as the space also doesn't wk. Can't Flow accommodate a space in the Excel column name?

// Wks
RemediationResponsibility eq 'Customer'

// Doesn't Wk
'Remediation Responsibility' eq 'Customer'
// Doesn't Wk
"Remediation Responsibility" eq 'Customer'
// Doesn't Wk
Remediation_x0020_Responsibility eq 'Customer'

[v-bacao-msft · Accepted solution]

Hi @Anonymous ,

Unfortunately, the Filter Query in List rows present in a table action does not support the use of fields with spaces in the field name as filter parameters.

This is a known limitation. If you want similar functionality to be supported by MS Flow, please consider voting for a similar idea:

https://powerusers.microsoft.com/t5/Flow-Ideas/Have-Consistency-in-on-Filter-Query-and-Order-By-on-Excel/idi-p/297122

Best Regards,

[yashm]

Hi, I am trying to do the same. Has this feature been added?

[_Julian_]

Hi, 
I found a workaround of this issue by dividing the task into two actions:
First you get the array with all rows from your excel file with the List rows present in a table action; second you filter it with the filter array action. You can edit it in advanced mode to even add multiple conditions with and or or logic.
Let me know if this was helpful to you

[skerbel]

Hi there! Can you explain further how you filtered the data to correspond to your rows or columns with spaces in them? 

[_Julian_]

Hi,

For this example I have an excel file with a column called "Column Name with Spaces". So I get the array of all entries in Table 1 of said Spreadsheet with the List rows present in a table action. Then I used the Filter array action to filter for the value 1 in the specified column. Note that you might have to edit in advanced mode as shown here, as the filter action is sensitive to the type of value you are filtering for (here: type string). The basic mode can sometimes be a little difficult for that.

After the flow ran, the inputs of the filter array action should show you the whole array content, the output should only return the filtered array entries.

Hope this helps 🙂

[lauratn]

@_Julian_ Your workaround helped me a lot. Thanks!!

[ericwhitley]

This is a decent workaround but slow when dealing with 20 thousand rows.  I would prefer a solution to do the filtering in List Rows present in a table.

[_Julian_]

Yes it is not ideal but the way I see it, it is only going to get better once they add the feature for this connector. Until then you will have to do some work around or if possible rename your columns for better performance. 

The solution I proposed earlier in this thread is also not going to work for 20 k rows as List rows present in a table returns max 5k rows without a filter. There are however severla workarounds for this issue.