Unanswered

Excel filter query, column name has special character

(0) Share

Report

Posted on by Community Power Pla...

Hi all,

I want to list rows present in an excel file and apply a filter query to it. However, the column name in the filter has special characters. This I can't change.

Its currently Q1. Date:

and my filter query is Q1. Date: eq CourseDate (CourseDate being a user input)

I understand I need to replace these special characters with unicode to allow it to work as expected. Can someone help please?

Is it something like Q1_x002E__x0020_Date_x003A_ eq CourseDate

Thanks

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Paulie78 8,422 Moderator on at

Like (0)

Report

Try:

Q1_x002e_ Date_x003a_

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks for the reply @Paulie78. No joy, I get the following error:

Syntax error at position 21 in 'Q1_x002e_ Date_x003a_ eq '30/09/2020''.
inner exception: Syntax error at position 21 in 'Q1_x002e_ Date_x003a_ eq '30/09/2020''.
clientRequestId: c8a4b107-a8fb-444d-ae83-8e44ed502bc3.

Does the space not also have to be replaced by unicode?

Was this reply helpful? Yes No
Verified answer

Paulie78 8,422 Moderator on at

Like (1)

Report
So I tried this out, with an Excel table with the same column name, I could not get the filter query to work. If you can figure out a way to get it to work I would be interested to know what it is. Instead I put the output from Excel into a select to make the column names easier to work with, then put that into a filter:

If you want the dynamic content back then you can add a parse JSON step. Just a warning, your query probably wouldn't have worked anyway (in the way you imagined). Because your excel dates are actually integers, not dates.

Edit: The expression on the left side of the filter is:

item()['Q1_Date']

Was this reply helpful? Yes No
IMAX99 55 on at

Like (0)

Report

I have the same issue 'special characters in column headers' but I have over 100 columns and the number varies. Any thoughts on how to handle this as using Filter array would require editing the workflow whenever new columns are added to my Excel table.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

The idea sound great, but how did you manage to add dynamic values (columns) to mapping? The only dynamic values I get are "body" and "value" :

Was this reply helpful? Yes No