Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Sometimes trying to extract data from a column can be difficult, so why not let Power Query help? Microsoft has put significant effort into a tool called "Columns From Examples" where you can define and refine the output, and let Power Query build the formula to get the results.
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.Create Columns From Examples.xlsx
19.3 KB Create Columns From Examples - Completed.xlsx
Create Columns from Examples
An overview of Power Query’s ability to generate a new column based on sample data
When to use
When you have a column of data and want to extract from or modify it, but don’t know how to get started
- Create a new query to bring your data to Power Query
- Go to the Add Column tab --> Column From Examples
- A new blank column will appear on the right side of the query table
- In the first cell, start typing in a sample of the data you would like to extract, and press Enter
Working with and refining example results
- If the column looks like it contains the data you want:
- Double click the header to rename it and then click OK
- The new column will be added to the query table
- If the data generated in the sample column is incorrect on some rows:
- Refine the results by typing over one of the incorrect results
- You may need to refine the result several times
- If the sample column fills all remaining rows with null values:
- This means the pattern is too complex and no refinements will help
- Try breaking large, complex patterns down into smaller columns and then building the results from those columns
Hints & tips
- This feature is NOT available in non-subscription versions of Excel 2016, but is available in:
- Excel 2010 and 2013
- Subscription versions of Excel 2016
- Power BI Desktop
- The formula Power Query generates for populating the new column is displayed at the top of the Query Editor
- You can control which columns are being used to generate the data by (un)checking the boxes at the top of each column in the Column From Examples interface
- Unlike Excel, you can remove the columns used to generate the new column without causing #REF! errors
Lesson notes are only available for subscribers.