cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhenderlight
Helper III
Helper III

Filtering text data from Excel not working

I have a column in Excel that contains text strings (which comes from an MS Form):

IA/Para (to address work completion)
Counselor (to address SEL/Wellbeing/provide resources)
Attendance supervisor (to address inconsistent attendance)
Admin (to address engagement strategies)

 

I want to filter a DataTable based on those data so I have a Dropdown control (named Assistance) with Items property set to:

Distinct(Tracking, 'Asking for assistance')

 

I then set the Items property of the DataTable to:

Filter(Tracking, 'Asking for assistance' = Assistance.Selected.Result)

 

And it doesn't work.  In the editor I get a syntax error which I can't seem to copy to the clipboard.  And at runtime, I get no data.

 

I looked at the documentation and it says to use Assistance.Selected.Value, but that is not valid since I am using a Distinct.  It seems like it could be something about text needed extra quotes or something, but I have tried concatenating quotes around things and that didn't help either.  This should be a straightforward scenario.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Wow.  I just changed the column name from Asking for assistance to Askingforassistance and everything started working.  Surely not.  That's pretty lame.  I am using an MS Form to capture the data and Forms uses the question names as the column names so I shouldn't have to have no spaces in my column names for this scenario to work.

View solution in original post

3 REPLIES 3
rubin_boer
Super User
Super User

 

 

 

 

hi @mhenderlight 

 

Have a look at this

rubin_boer_0-1614276457204.png

The collection (Assistance) has a column Called Request

Collect(Assistance,
{Request:"IA/Para (to address work completion)"},
{Request:"Counselor (to address SEL/Wellbeing/provide resources)"},
{Request:"Attendance supervisor (to address inconsistent attendance)"},
{Request:"Admin (to address engagement strategies)"}
)

The dropdowns items = Distinct(Assistance,Request).Result

 

The datatble witll have Items = Filter(Assistance, Request = YourDropdown.Selected.Result)

 

Hope it helps

 

@rubin_boer 

Thanks.  Where do I put the Collect call?  I tried putting it in the OnVisible of my screen, but the dropdown doesn't have the items.

 

Oh also, I can't have the datatable items set to what you have above, I have to use my actual excel table:

Filter(Tracking, 'Asking for assistance' = MyDropdown.Selected.Result)

and that puts me back into the same syntax error situation as before.

 

Actually, I don't have a problem getting the dropdown to be populated with my values using:

Distinct(Tracking, 'Asking for assistance')

 

My problem is with the datatable.items.

Wow.  I just changed the column name from Asking for assistance to Askingforassistance and everything started working.  Surely not.  That's pretty lame.  I am using an MS Form to capture the data and Forms uses the question names as the column names so I shouldn't have to have no spaces in my column names for this scenario to work.

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,504)