Flash Fill in Excel 2013 was a neat party trick. But the Column From Examples feature in Excel Power Query is far better. See how Column From Examples works.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2266. Power query column from examples.
Hey welcome back to MrExcel podcast, I’m Bill Jelen. You know Excel 2013 introduced Flash Fill and Flash Fill was cool and all.
But you know the problem with Flash Fill is it doesn’t tell you how it’s filled, right.
I mean these aren’t formulas out here. The underlying data changes, you know, so what right. So, power query now has the ability to Flash Fill but it’s so much better. I want to make this into table here, control T, data, get data actually appear from a table.
Alright. And we’re going to do Flash Fill now. But what I want to do now is I want to take this date and I want to make this date into a month or something like that. Now, alright. In Excel that’s equal month but if I want the year, it’s going to be the text function so I’m going to come here to add column and I’m going to use column from examples. Column from examples. That first one is January 2018 so I’m going to put January and they offered me month name from date and I choose that, click ok, choose January and then click ok and it fills it in. But it doesn’t just fill it in, it tells me what formula I would use for that in the future. So when I come here to the advanced editor, this is date dot month name, open parenthesis, date, close parenthesis, right.
And this is case sensitive which is super annoying. In Excel, I can use equal text, equal upper case text, equal lower case text, the T and the E and the upper case and the X and the T lower case but in Power Query, they’re super, super picky about getting just the D, just the M, just the N, capitalized which I hate because I would have to always go back to the power query function reference online to figure out. Well I don’t have to do that anymore, alright. Let’s say I just want to get the first three letters of sector, let’s try that again. So we’ll come here, add column, column from examples, and I’ll type MAN and then I will press Enter, now didn’t get it, so then I type HEA and then TRA, alright. There we go. Now they figured out that I want the first characters.
I click okay, in Excel it would have been equal left, comma 3, right? But here in Power query, it is text dot start sector comma 3 text dot start. What a weird function language this M language is. Alright. Flash Fill been around in Excel for a long time. It is fine but the column from examples feature here in power query is so much better because it’s doing what Flash Fill would do and it’s giving me the formula. So that way, if something changes the next time you update this that will change as well. Alright, the new column from examples feature in Excel, very, very cool set of functionality.
Hey, want to thank you for stopping by. We’ll see you next time for another MrExcel netcast from MrExcel.
Hey welcome back to MrExcel podcast, I’m Bill Jelen. You know Excel 2013 introduced Flash Fill and Flash Fill was cool and all.
But you know the problem with Flash Fill is it doesn’t tell you how it’s filled, right.
I mean these aren’t formulas out here. The underlying data changes, you know, so what right. So, power query now has the ability to Flash Fill but it’s so much better. I want to make this into table here, control T, data, get data actually appear from a table.
Alright. And we’re going to do Flash Fill now. But what I want to do now is I want to take this date and I want to make this date into a month or something like that. Now, alright. In Excel that’s equal month but if I want the year, it’s going to be the text function so I’m going to come here to add column and I’m going to use column from examples. Column from examples. That first one is January 2018 so I’m going to put January and they offered me month name from date and I choose that, click ok, choose January and then click ok and it fills it in. But it doesn’t just fill it in, it tells me what formula I would use for that in the future. So when I come here to the advanced editor, this is date dot month name, open parenthesis, date, close parenthesis, right.
And this is case sensitive which is super annoying. In Excel, I can use equal text, equal upper case text, equal lower case text, the T and the E and the upper case and the X and the T lower case but in Power Query, they’re super, super picky about getting just the D, just the M, just the N, capitalized which I hate because I would have to always go back to the power query function reference online to figure out. Well I don’t have to do that anymore, alright. Let’s say I just want to get the first three letters of sector, let’s try that again. So we’ll come here, add column, column from examples, and I’ll type MAN and then I will press Enter, now didn’t get it, so then I type HEA and then TRA, alright. There we go. Now they figured out that I want the first characters.
I click okay, in Excel it would have been equal left, comma 3, right? But here in Power query, it is text dot start sector comma 3 text dot start. What a weird function language this M language is. Alright. Flash Fill been around in Excel for a long time. It is fine but the column from examples feature here in power query is so much better because it’s doing what Flash Fill would do and it’s giving me the formula. So that way, if something changes the next time you update this that will change as well. Alright, the new column from examples feature in Excel, very, very cool set of functionality.
Hey, want to thank you for stopping by. We’ll see you next time for another MrExcel netcast from MrExcel.
