There are plenty of ways to enrich your data in Power BI. But if you want to enhance that data, and learn a bit of code you may not have previously known, then Column From Examples is what you should be using.
So what makes Column from Examples worthy of your valuable time. Let’s take an annoying problem as our example.
We have a data set with date/time. This is good, we like date/time. However there is a strange country on this planet which refuses to use the correct date format. It’s the MM/DD/YYYY conundrum. And yes, we can change this setting in the Power BI settings, but for the sake of this post, and because this is such a ludicrous way to write the date, I refuse to do so.
When we try to convert this to Date/Time, this happens
Well, ‘Column From Examples’ of course. Lets make some Power BI magic.
Select the column and from ‘Add Column’ we can select ‘Column from Examples’ & ‘From Selection’
At first, when we double click, the choices are pretty limited. Pretty useless in fact.
But lets throw in some data and see what happens. I’m just going to manually type in the format I want.
After it detects the first variable, PBI then gives me the format I need. I now have a lovely column of data which I can convert to Date format (I could have also entered the time and converted to Date/Time…but I forgot).
But that’s not my favourite part!
So what’s the very cool part? Because actually, we could have just done that with a bit of M, right? Right. But let’s be honest, Power BI is a tool used by professionals who know a terrifying amount, and beginners who want to learn. This is the beauty of a powerful tool designed also for ad hoc analysis.
If we now click on the cog, it doesn’t take us back to the place we did our original entry, but instead it will show us a standard ‘Custom Column’ box. not only can you see the M code you kind of did write, but didn’t, you can also copy it and change the column reference for any further columns you have with this nonsensical date format.
Well this is just a small example of the functionality of Column From Examples. There are multiple reasons to achieve the same result, and this certainly could have been achieved in a different way. However, what is important is that more people are aware of this functionality. Even if you can’t be bothered to write out multiple conditions for in an Conditional Column, Column From Examples is the way to go. In certain situations it really is your best friend. So give it a call and it will help you out and help you learn.