cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LittleBird23
New Member

Excel filter query, column name has special character

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

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User III
Super User III

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:

oDataFilterExcel.PNG

 

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']

View solution in original post

3 REPLIES 3
Paulie78
Super User III
Super User III

Try:

Q1_x002e_ Date_x003a_

LittleBird23
New Member

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?

Paulie78
Super User III
Super User III

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:

oDataFilterExcel.PNG

 

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']

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (43,077)