Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to transform data using Power Query Column From Examples. Let’s get started
SAMPLE DATA
In the screenshot below, we have a single column of Emp Name. Our goal is to transform the name such that the last name will come first with a comma while the first name will come next. Note, the dataset below is already formatted as an Excel table using CTRL + T
OPERATION
The first thing we need to do is to get the dataset into the Power Query editor. To do that:
- In the Get & Transform Data group of the Data tab, click on From Table/Range
OR
- Right-click inside the dataset and select Get Data From Table/Range
- In the Power Query editor, click on Column from Examples located in the General group of the Add Column tab.
- In the Custom column to the right, type in: Callahan, Laura and Click Enter
- In the grid below the first example performed above, type in: Peacock, Margaret and click Enter
After typing the second name based on the first example provided, we should see a ghost list just like the one in Flash Fill. All we need to do is to Click OK or Enter on the keyboard. That would fill all the names in the specific order we set out as seen below:
Once we are done, we can Close and Load to Excel worksheet as seen in the screenshot below:
See you in the next tutorial