Sharepoint Input Tool Data Cleanup

[fitzviz]

I have pulled my data in using the Sharepoint input tool, however a bunch of my columns have the internal code pulled through from pick lists, for example "146;#United States" where all I want in my file is "United States" Is there a quick and systematic way to clean this up in Alteryx other than parse out to text to columns then using data cleansing? Essentially I just want to truncate everything before the #... Assuming this is a common question with Sharepoint data? Thanks!!

[NicoleJohnson · Accepted solution]

You should be able to use Regular Expression formula in the Multi-Field tool to accomplish this...

1. Add Multi-Field Formula tool.

2. Select the columns that have the internal code pulled through from pick lists. (Uncheck the box to Copy Output fields to a new field if you just want the formula to fix them in their original fields).

3. Use this expression to remove the initial numbers and ;# from before the actual field values:

REGEX_Replace(tostring([_CurrentField_]), "\d*;#(.*)","$1")

For all fields that are checked, it will use this expression to keep only whatever is in the parentheses (marked field). Let me know if that works for you! RegEx is a beautiful thing... :)

NJ

[fitzviz]

Wow thanks NJ, this was my first workflow in Alteryx and I am impressed. Do you have any recommendation for training or resources that you found particularly helpful for learning some of these formulas and tricks? 

[NicoleJohnson]

Honestly, the Community in general has been a terrific resource - So many "how-to" postings, and the interaction in the discussion boards for asking for assistance are possibly the best I've ever seen. I have been a huge fan of the Weekly Challenges as a way to brush up on skills from basic to advanced, and a great way to see how many different creative ways people can use Alteryx to get to solve the same challenges... I highly recommend checking them out! Weekly Challenge

For more "official" training, check out the live webinars too, usually 1 or 2 a week available. I've taken a tips & tricks one, a macros one, and a parsing one, all have been fantastic. Live Training

For the SharePoint solution I provided though, that one was mostly trial and error :) I have found way too many scenarios lately requiring creative parsing skills, and RegEx has become my best friend. Let me know if you'd like some links to resources on that particular area, I have a list that I was given when I first started playing around with Regular Expressions, and they've pretty much changed my life :)

So glad to hear that this first foray into Alteryx has been such a success! Welcome to the (incredibly awesome!!) Community, and let us know if you get stumped on anything else! Good luck!

Cheers,

NJ

[fitzviz]

Yes, please share that list if you can! I am trying to figure out an expression to clean up a bunch of nasty HTML formatting code that pulled through from a comment field in Sharepoint. Need to do a lot of study up on RegEx!

I will also check out the challenges and trainings, thanks again. 

[NicoleJohnson]

Absolutely! See below for links to RegEx articles, these were super helpful when I first started out playing around and knew basically nothing about Regular Expressions :) Now, they're my favorite tool! Check out the other Parse category tools as well, like Text to Columns and XML Parse, you might find some creative uses for those as well. Happy parsing!

* RegEx list originally provided to me by the esteemed Alteryx guru, @SeanAdams... :)

Loads of good beginner regex on YouTube:

- https://www.youtube.com/watch?v=DRR9fOXkfRE for examples

Additionally there's a few good guides out there and helper sites

- https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

- http://www.rexegg.com/regex-anchors.html

- http://regexr.com/

- http://regex101.com

- http://www.rexegg.com/regex-quantifiers.html

- https://regexone.com/

[kishore02]

Hi  Nicole Johnson

I have small doubt in Alteryx can you help me out of this.

1. I have connected my share point data from folder A in Alteryx.

2. I have got the output in another folder B in share point.so now i want  to delete data of folder A in share point when ever the data is copied to folder B.so automatically folder A should be deleted in share point

can you please help me in this