Filter Wizard doesn't work with numbers (Excel) - foundation training Lesson 9

Learning Hub Academy Feedback
1

The Filter Wizard in the Filter Data Table activitiy “ignores” the numbers in the Excel spreadsheet.
In case I use a number in the Value field (with or w/o quotations as 2 or “2”) it’s always ignored (the filter just doesn’t work). However if I use as a value a text string (a true text as “abcd”) it works.
I have tried to load the answer file (Lesson 9 Practice 2) and got “Missing or invalid activity” where the Filter Data Table activity is supposed to be.I don’t understand what I’m doing wrong. Can anyone help me ?

2
  1. What operation are you trying to perform ? Try “Contains” if you are using “=”, it may work.
  2. If that doesn’t work or satisfy your requirements, loop through the table using for each row activity and perform your action.
3
  1. In fact, the task are to filter the ID column by the values greater than 10. It did not work at all.
    I just decided to simplify it and just filter the rows with the ID=value.
    The fact hat puzzles me is that if value is a digit such as 1,2,3,… etc. it doesn’t work. It doesn’t work if I use
    “Contains” either. However if I change the ID column in the Excel file to strings such as “One”, “Two”, “Three”
    etc. both contains and equal (=) work ! I don’t understand it.
  2. I did it and it changed absolutely nothing.
    I guess something might be not right in the Studio itself as I cannot load the tutorial sample with the Filter
    Wizard (see the original post). It displays “Missing or invalid activity” where the Filter Data Table one is
    supposed to be.
4

I’m having the exact same problems:

1. filtering on age < 30 does not work: I get either only the header row (Keep 1 < 30) or all rows inclusive the header row (Remove 1 < 30)

example:
data:
image
filter:
image
results to:
image

digging deeper: ages do not correspond to original ages - what is going on???

2. filtering on name begins with "AN" works

example:
data:
same as above
filter:
image
results to:
image

digging deeper: ages do not correspond to original ages - what is going on???

3. not able to get downloaded example working

as mentioned in Lesson 9 - Excel and Data Tables - Workflows.zip Error in downloaded xaml 'errors in the xaml' - #7 by charliefik

How can I filter on numbers?
Why are the ages changing?
(and how can I get downloaded example working?)

Anyone any suggestions?
Thanks!

5

Digging deeper into the changing ages:
It seems the ages are changed randomly while reading the excel file:

image
image

I thought maybe my workflow is not oké:

image

so I changed it to:

image

but the ages are still corrupt:

image

Any help is much appreciated!

6

Hi

I tried the same sample Excel spreadsheet and there were no problems with the Age inconsistency although the Filter wizard did not work.

Please see the workflow below:

7

Hi @MichaelK

Thanks for looking into this.

I noticed that in the Read Range activity you selected everything (“”) and I selected a specific range (“A1:E11”).
So, I retried with selecting everything, but the age inconsistency still exists.
The weird thing is that the ages are the same as in the excel that stays open, but these are different from the original excel (take a print screen before you run the script and compare)

Digging a little deeper into this, I found that there’s something weird going on with the excel itself:
When I open it I get:
xlsx_before_Bewerken_Inschakelen

As you can see I get a Protected View-message (in Dutch). It says:
ProtectedView
When I click on the button “Bewerken inschakelen” (Enable editing), I get different ages in the excelfile:

xlsx_after_Bewerken_Inschakelen
xlsx_after_Bewerken_Inschakelen742×513 59.7 KB

So I think the problem is in the excel-file and not in UiPath.

8

I finally found the origin of the changing ages: they contain formula’s !!! People of UiPath tutorials, you could at least have told us that! Would have saved me a lot of trouble.

image
image718×114 5.43 KB

And what the filtering on numbers issue is concerned: it seems you need to write your number as a floating point number.
Found the solution in this thread: Lesson 9 2B Revamped
with a big THANK YOU to @Indra

9

Interesting that in my sample the Age is counted by a use of a different formula although it’s the same file! It’s a pseudo random. Please see below.

The floating number solution works perfectly well however I don’t think this is correct in respect to the UI Path Studio. In addition, it is in disagreement with the study material!

Should be specified that the Value in the Filter Wizard must be a floating number however there is no information on this at all.

image001.png748×182 8 KB

10

Hi @MichaelK

Actually, it’s the same function, only the Dutch version, as you can see here: Excel function name translations - English-Dutch.
Why the people at Microsoft ever took the decision to translate formula’s, I don’t know. It makes things harder sometimes. E.g. It took me some time to figure out in an earlier exercise, where we had to sum up two values using Excel-formula’s, that in UiPath I need to use the English formula’s, not the Dutch ones. But my Excel-files will still show the Dutch ones. So now I have to become bilingual in formulas as well :wink:

I totally agree on your remarks on the filter wizard and the floating number solution.