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

Datatable not displaying data filtered using text value

Hello Community,

 

This is my source Table (Sheet1):

REF_DATEREF_VAL
201601022300
201601022300

 

On the receiving DataTable I use this filter:

Filter(Sheet1,REF_DATE="20160102")

I also tried: Filter(Sheet1,REF_FECHA=20180430)

 

However, I get no Data, please see attached image.

 

Hope someone can help.

1 ACCEPTED SOLUTION

Accepted Solutions
soloposmenu_PA
Level: Powered On

Re: Datatable not displaying data filtered using text value

Ok, working on it, it is a combination of things, now both max(DatePicker1_1. and min(DatePicker1) dates are returned.  The problem now is to have, the VENTAS column sum all values, instead of just showing them.

 

v2 DataTable from PoweApps.png

 

Filter(Filter(Sheet1,DEPTO=Text(TitleDepto)),

DateValue(FECHA,"[$-en-US]dd/mm/yyyy") >= DateValue(Text(DatePicker1.SelectedDate),"dd/mm/yyyy") && DateValue(FECHA,"[$-en-US]dd/mm/yyyy") <= DateValue(Text(DatePicker1_1.SelectedDate),"dd/mm/yyyy"))

 

It is a learning curve, hopefully the Format function will be on a upcomming release, so as to avoid mixing various functions to return a value.

 

Thanks Again Nick

 

View solution in original post

4 REPLIES 4
johnsonjohn
Level: Powered On

Re: Datatable not displaying data filtered using text value

Is your filter function in the Items property of the Gallery you are using to display these records or somewhere else?

What is the type of REF_DATE ?

 

Have a look at the scenario below

 

Filter( IceCream, "chocolate" in Lower( Flavor ) )Returns records where the word "chocolate" appears in the Flavorname, independent of uppercase or lowercase letters.
Community Support Team
Community Support Team

Re: Datatable not displaying data filtered using text value

Hi @soloposmenu_PA,

Could you please share a bit more about the REF_DATE column in your source Table (Sheet1)?

Have you formatted the REF_DATE column value as a Date value in your source Table?

I have made a test on my side, and don't have the issue that you mentioned. The screenshot as below:1.JPG

Set the Items property of the Data Table to following:

Filter('20181228_case12', Text(DateRequired) = "12/28/2018")

If you have formatted your REF_DATE column value as a Date value in your source Table already, please take a try to set the Items property of your Data Table control to following:

Filter(Sheet1, Text(REF_DATE) = "20160102")

Or

Filter(Sheet1, "20160102" in REF_DATE)

You could also consider take a try to save your Sheet1 data source into a Collection, then take a try with above formulas, check if the issue is solved. Please take a try with the following formula:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, Sheet1)

Set the Items property of your Data Table control to following:

Filter(RecordsCollection, Text(REF_DATE) = "20160102")

Or

Filter(RecordsCollection, "20160102" in REF_DATE) 

 

If the issue still exists, please consider take a try to re-create a new connection to your source table (Sheet1) from your app, then try above formulas I provided, check if the issue is solved.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
soloposmenu_PA
Level: Powered On

Re: Datatable not displaying data filtered using text value

Thank you for your help. REF_FECHA is a string value yyyymmdd. FECHA is a date value dd/mm/yyyy.

 

I was able to get the FECHA working with the = operator, like this:

Filter(Filter(Sheet1,DEPTO = Text(TitleDepto)),Text(FECHA) = Text(DatePicker1.SelectedDate,"[$-en-US]dd/mm/yyyy"))

 

However, when I use the >= operator, I get the error of "Invalid argument type. Expecting a number value"

Filter(Filter(Sheet1,DEPTO = Text(TitleDepto)),Text(FECHA) >= Text(DatePicker1.SelectedDate,"[$-en-US]dd/mm/yyyy"))

 

Please see bellow. https://powerusers.microsoft.com/t5/media/gallerypage/image-id/47352iA5613D0B4377530D

 

soloposmenu_PA
Level: Powered On

Re: Datatable not displaying data filtered using text value

Ok, working on it, it is a combination of things, now both max(DatePicker1_1. and min(DatePicker1) dates are returned.  The problem now is to have, the VENTAS column sum all values, instead of just showing them.

 

v2 DataTable from PoweApps.png

 

Filter(Filter(Sheet1,DEPTO=Text(TitleDepto)),

DateValue(FECHA,"[$-en-US]dd/mm/yyyy") >= DateValue(Text(DatePicker1.SelectedDate),"dd/mm/yyyy") && DateValue(FECHA,"[$-en-US]dd/mm/yyyy") <= DateValue(Text(DatePicker1_1.SelectedDate),"dd/mm/yyyy"))

 

It is a learning curve, hopefully the Format function will be on a upcomming release, so as to avoid mixing various functions to return a value.

 

Thanks Again Nick

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,331)