Replicating Text to Columns in PowerPivot

PowerPivot comes standard with Excel 2016, and was available for versions as old as Excel 2010 as an add-in. It allows for advanced calculations to be run in worksheets with a greatly expanded data capacity. If you've got a spreadsheet with so much data that Text to Columns is freezing up, try running an alternate Digital Analytics Expression (DAX) in PowerPivot. Just follow these steps:

1. In Excel 2016 go to the Power Pivot tab and click on Manage.

2. Assuming you're working with a .csv file, click on 'From Other Data Sources' in the Get External Data section.

3. Select Text Files for the Data Feed, click Next, browse to the .csv file [after making sure the types are set to 'Comma separated files'] . Click Finish and the import process will begin.

4. After the import is finished press close, and you'll see that you now have the data arranged in spreadsheet form in PowerPivot. In this example we'll insert six new columns to the right of the one named 'Issue'.

5. Now we're going to enter this formula in the new Calculated Column 6:

=PATHITEM(SUBSTITUTE(Consumer_Complaints[Issue], ",", "|"), 1)

This formula was created by Michael Amadi and posted to this site, http://www.nimblelearn.com/Blog/Post/14/Using-DAX-to-Split-Delimited-Text-into-Columns . 'Consumer_Complaints' refers to the name of the Power Pivot table as shown on the tab below. The SUBSTITUTE formula is finding the commas in the column specified in brackets, Issue, and then PATHITEM returns the first instance of data.

Enter the formula in the formula bar, not in an individual cell. It will take a moment to calculate, and fill in the entire column. Now you'll have the data parsed out.

See the below demonstration video from my YouTube channel,