Remove occasional row in iteration project

1

Hi there,

I am trying to run a project through an iteration and am having difficulty removing a row that occasionally shows up at the end of a file.

The project imports a .CSV file, removes any row containing ‘Totals’ or ‘TOTALS’ in the column [PERIOD] or [Period], then exports the file as a .csv.

When I tried filtering out rows with ‘Totals’ the [Period] column and ran the iteration I received an error that [Period] was not present since the data files use [Period] and [PERIOD] interchangeably.

I tried filtering by several conditions, most recently by [PERIOD] or [Period] != ‘Total’ or ‘TOTAL’ or ‘Totals’ or ‘TOTALS’ and have received a similar errors.

2

Standardize column names first. For this create a table with 2 columns: in one column are possible names, and in the other the standard name. Then use “Rename with lookup” transformation to change column names to standard ones. See the screenshot below.

image

Then, in the filtering condition bring the value to the upper case before comparison. So that it becomes

upper([PERIOD]) = 'TOTAL' or upper([PERIOD]) = 'TOTALS'

If you have multiple values to compare with you can also use the in() function.

in(upper([PERIOD]), 'TOTAL;TOTALS;TL;SUBTOTAL', ';')
1 Like
3

Thank you! @dgudkov

4

You’re welcome!