4 Replies Latest reply on Jul 20, 2018 12:25 PM by Rachel Horne

    What is the best method to clean data across multiple fields' rows (i.e., the entire data set) without requiring the field name?

    Rachel Horne

      I have survey data provided to me that has over 200 fields.  Within some of the fields (e.g., about 20+) are likert scale rankings of 1-10.  When the questions are asked they use the 1 and 10 ratings to reflect the positive and negative do text is added to the numbered responses.


      For example, the ratings look like this:

      When I am using the data in Tableau, I always need to create a manual sort of each of the fields I am using to reflect the order should go numerically 1-10 rather than sort numerically then alphabetically.


      In Tableau Prep, column C above would like this prior to a calculation or renaming of the row:

      What I would like to do is have any row with a 1 or a 10 displaying revert to that only by stripping away the text and leaving only the number.


      I know I can do this manually by double-clicking on each row or use 'find and replace' in Excel but was hoping there is a method in Prep that allows me to remove the text for responses 1 and 10 across multiple fields.  Cleaning the data and to 'remove text' can also be done by field, but then I also lose the response of 'don't know.'  I get this data monthly so the manual cleaning takes up a lot of time.


      I have attached some dummy data to reflect a few of these fields.


      Thanks in advance for your ideas!