During my session today at The Data School, I was introduced to the world of Power BI and Power Query. Power BI is a data visualization software launched by Microsoft back in 2015, and has been making significant strides to compete with established industry leaders like Tableau. On the other hand, Power Query has emerged as a rival to tools like Tableau Prep and Alteryx. What sets Power Query apart in my eyes is its seamless integration within Power BI, enabling a fluid transition between the two tools, where as Tableau Prep requires exporting the data before it can be used in Tableau Desktop, and Alteryx completely lacks a standalone data visualisation tool.
Whilst exploring the capabilities of both tools, one feature that particularly caught my attention was Power Query's 'Column From Examples,' a powerful function that streamlines data transformation.
Column From Examples
Overall, the aim of the 'Column From Examples' tool is similar to that of straightforward column splitting. However, it distinguishes itself by eliminating the need to specify the character(s) to split the columns at, as well as how many times to split and which occurrence of the character(s) to split at. Instead, this tool prompts you to input the desired outcome you seek from a specific column, using a single example. As its name implies, "Column From Examples" leverages this sample result to autonomously craft a query capable of extracting that specific portion of the field for all the records in the table. In cases where the tool doesn't achieve the desired outcome for every row the first time round, you have the flexibility to provide additional examples for the miscomputed rows until it attains the precise result you're aiming for.
Example use case
To access the 'Column From Examples' tool in Power Query you first need to have your desired table open. For this example I will be using the 'Orders' table from the 'Sample - Superstore' dataset.
At the top of the screen, navigate over to the 'Add Column' tab. The 'Column From Examples' should be the first button in the menu.
With the tool selected, you should notice a new column appearing on the right most side of your screen.
From here, simply enter an example of your desired value into one of the rows in the new field, which in my example is the last five digits of the 'Order ID' field. Repeat this step as many times as needed in other rows until Power Query achieves your desired value for every row in the table.
From here you can change the field name and data type, and continue on with the rest of your data transformation!