cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mitchgramazio
Level: Powered On

Excel Data Filtering based on a Text "Date" Column

Hello all,

 

I've done a bit of searching on the site to make sure I'm not repeating a problem that's already been solved but I couldn't find a solution that worked for me.

 

I've got a column, in Text Format, titled "Date_Logged". I also have two DatePicker controls "contStartDate" and "contEndDate". I am trying to Filter() a table based on something being between the two dates picked in the controls. Currently I have:

Filter(
    Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") And contEndDate.SelectedDate >= DateValue("Date_Logged")
)

I am using the DateValue function for the first time in my experience here, so it's possible that the error lies there. I am getting a Boolean value error for the Filter() function.

19 REPLIES 19
Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

Hi @Mitchgramazio 

You have a bit of a syntax error in your filter.  The And is in all caps.  The actual And function is "And".

You can replace the AND with And and try that.  Also, you can use the && notation to provide the same And capablity...such as:

Filter(
    Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") && contEndDate.SelectedDate >= DateValue("Date_Logged")
)


I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

Thank you,

 

I actually made that change already once realizing it. I'm still getting the error described earlier though.

Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

Can you provide a screenshot of the error and the formula showing the red underlines and any relevant error message?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

Oh, and I just noticed you have quotes around your "Date_Logged".  Have you tried removing them?  On second glance, I believe that is possibly your issue.  You are trying to get the date value of "Date_Logged" (which would be nothing relevant).  But DateValue(Date_Logged) would be relevant.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

Attached is a screenshot. I've tried removing the quotations but nothing changed. I'm referencing a column in the Excel sheet which I believe requires the quotations for.

 

As an additional piece of information, this is in the Items property of the table.Screen Shot 2019-05-16 at 18.22.40.png

Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

Ah, now we're getting somewhere...that formula will not work that way.

 

You have a few conditions there based on the radio buttons and there are a few things you need to take into account.  First, your date range filter should go against the inner filter of the table you are searching.  Since that is a pain to put in over and over again, you can construct your formula like this:

Search(
   Filter(DiveLogs, 
          contStartDate.SelectedDate <= DateValue(Date_Logged) && contStartDate.SelectedDate >= DateValue(Date_Logged)
          ),
   Switch(Radio1.Selected.Value,
       "PERNER", TextInput3.Text,
       "Last Name", TextInput3.Text,
       "First Name", TextInput3.Text,
       "Hub ID", TextInput3.Text,       
       "Team", contTeam.SelectedText.Value,      
       "Location", contLocation.SelectedText.Value),
   Switch(Radio1.Selected.Value,
       "PERNER", "PERNER",
       "Last Name", "Name",
       "First Name", "Name",
       "Hub ID", "Hub_x0020_ID,       
       "Team", "Team",      
       "Location", "Location")
   )
      

In this case, we're filtering the DiveLogs table to start with and then placing that as the data source for the Search.  The second parameter is the text we want to search.  Since that varies based on your radio, the switch function is there to choose the right one.

The third parameter is the field we want to search.  Again, since that varies based on the radio, the switch function is there to choose the right one.

 

This could be crafted several different ways, but this should be pretty much what you're looking for.

Oh, and no...the fields should not have quotes around them when you are referencing them in this way.  Quotes around fields is only for certain parameters of some functions (like search).

 

Give the above a try and see if that gets you where you need to be...or at least closer.

 

I hope this is clear and helpful. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

Alright, I've replaced the code with the one you've provided. It returned an error saying that the Search function is expecting Text in the second paramter. I pruned through your code to make sure the assets matched what I am using in the application and it all checks out. I read more on how to use a Switch function and I see how that replaces the If syntax I had earlier. I'm unsure of why the Switch function isn't returning text values considering if a match is made, we've got it programmed to return a Text value from whichever radio button is selected.

Super User
Super User

Re: Excel Data Filtering based on a Text "Date" Column

@Mitchgramazio 

At a glance, without knowing your data specifically, I am suspecting "Hub_x0020_ID".  What kind of column is that.

You can ONLY use search on text columns.  You cannot use search to search a numeric column.

 

See if that is what you're up against.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Mitchgramazio
Level: Powered On

Re: Excel Data Filtering based on a Text "Date" Column

There is one column whose format is a custom number format and this preferrably stays this way. Am I able to use a Text() function or something similar?


Edit: I've removed both items that are not a generic Text format in Excel and the error remains.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 178 members 5,198 guests
Please welcome our newest community members: