Power Query is a data transformation and preparation tool within Microsoft Excel and Power BI.
In this tutorial, I'll be explaining the "Add Column from Examples", a handy feature you can use in Power Query as a beginner.
You'll often come up with a dataset that has columns that need to be split up. Something like a product ID or barcode may need to be split into separate fields.
In the example below, we want to to have the numbers in the product ID as their own field (e.g. 10000174).
There are many way to do this in Power Query but a great way to do this is with the "Add Column from Examples" feature. You can find this feature by right clicking on your field headers or under:
File > Add Column > Column From Examples
From here Power Queries built in logic will assume how you would like to transform your data based on the examples you type in. Here we want just the number, so I type the trailing number in the first row. Power Query picks up on that logic and automatically populates the remaining rows. Easy.
In the toolbar above, you can see how Power Query has completed the calculations by providing the code of it's native language of M.
This translates as, for the Long Product ID field, separate the field by finding the "-" in position 1. (There are two hyphens in this field but the first is represented at position 0). Writing this out would not be straight forward if you're new to Power Query.
You can do the same for name, addresses, and any other data field that requires trimming saving you lots of time and headaches.
This feature is iscredibly intuitive and user-friendly, making data preparation accessible to non-technical users. It saves time and minimizes errors in data transformation, allowing you to work with your data more efficiently. Whether you're a beginner or an experienced user, "Add Column from Examples" is a handy tool for simplifying your data processing tasks.
TLDR;
To use this feature, follow these steps:
- Open your data in Power Query.
- Go to the "Add Column" tab and select "From Examples."
- A new column will appear, and you can start typing examples of the data you want in this column. Power Query will automatically analyze your examples and generate a transformation formula.