Coolest Power Query Functions You Must-Learn 😍

There are a few very interesting Power Queries M functions that I happen to use over and over again and I want to talk about those functions. But more

importantly, I also want to talk about applications of those functions. Those

are real life data cleaning examples. I

also want to talk about a few ancillary functions that come along that will enhance your solution. The best part about this video is going to be a secret word that I will mention somewhere in this entire video package. And if you

can figure that word out and mention that in the comments under the video, I am going to give you free Goodly Insider access for the next 12 months. Goodly

Insider happens to be our newest PowerBI community learning experience. If you

haven't checked that out, I will leave a link below and you can check it out. But

nevertheless, pay attention to the entire duration of the video and you might just win the free Goodly insider access. In case you're interested, check

access. In case you're interested, check out the links. Let's just start the learnings of my favorite and most useful Ahab query functions. Let's go.

All right, people. Let's just start by doing the work that you actually do first in Power Query, which is loading some data inside of your query. And

typically, if you're connecting to a folder with some Excel files, the way that you would go inside of the folder is by going through the data tab, clicking on get data from files from folder. And that's all right. Now, if

folder. And that's all right. Now, if

you are inside of the Power Query window, what you would do probably is that you go to the new source, and you'll say file, and I want to connect to a folder, which is the same thing.

Now, this particular action triggers a formula called folder.file. files and

we're going to write that and I'll show you the difference between folder.files

and another function that I want to talk about. So let's just say that I'm in the

about. So let's just say that I'm in the source tab. At the moment my query is

source tab. At the moment my query is completely blank and I will start writing the formulas because hey I'm obsessed with the M language so why not?

So I'm going to say equals to folder dofiles which is the standard function that Power Query uses. So folder dofiles I will start the bracket within the bracket I am just going to go ahead and feed some path of the folder. I will

just stick that in. Ctrl +V to paste it and I'm just going to press enter. What

the folder dofiles function inherently does is it not only takes a look at the folder, but also every single subfolder within that particular folder, which is kind of good, but it's not good if you're trying to make your query efficient. Let me show you what do I

efficient. Let me show you what do I mean by that. Here is the folder that you can take a look at that I connected to, which is coolest power query functions, the video that I'm recording at the moment. In this particular folder, sure enough, I have this Excel

file. I have the recorded intro and I

file. I have the recorded intro and I also have a subfolder. And now what Power Query is going to do is if you're using the folder.files files function is that it is going to take a look at all the files that exist in the folder

directly but also the files that exist within the subfolder. So if I happen to open the subfolder right here, there are two text files which are empty text files and you're going to take a look that it has also read those two text files right here. Now it's all right in

case you want to take a look at all the ingredients and the ingredients of the subfolders as well. That's fine. But in

case you want to compress your query and you don't really want a lot of rows to be loaded which obviously slows down your query instead what you can do is rather than actually using this particular function called folder.files

you can rather use the function called folder.contents and that's about it. Now

folder.contents and that's about it. Now

what this function is going to do is it's going to package all the subfolders into a single row and not load the files of those subfolders. So if I just maybe happen to click on this actually this is contents the plural. What you're going

to see that all the subfolders are going to be listed as a table here and you have all the binaries uh right here. In

case you're working with any particular function that is going to run through all of these files, you can actually speed up your query by using folder.contents instead rather than

folder.contents instead rather than folder.files. The next functions are a

folder.files. The next functions are a bundle of two functions which is list dot select and list.transform. Super

cool functions to do some advanced data transformations. Let me show that to you

transformations. Let me show that to you real quick. So I've got some data right

real quick. So I've got some data right here. Simple data. We have the course

here. Simple data. We have the course name, the first student, the second student, the third student, the fourth student, and some marks right here, which is pretty cool. Now, what I want to do is perhaps I want to be able to work with all the students right here.

So, column number one all the way through column number four, the headers or the titles of the columns that contain the word student. And that's

what I would want to do. What we want to do with these columns, we will decide later. But for some reason, I want to

later. But for some reason, I want to extract these columns. And for such transformations, I am actually going to use a function called list dot select and list.transform. You will be amazed.

and list.transform. You will be amazed.

Take a look. So the very first thing that I want to do is since I want the selective ability to choose the columns that I need that contain the word student for the moment I'm going to start extracting all the names of the

columns no matter what the column name is that means this column name along with the four student column names along with the marks column name and if there are any other column name I want that as well. So what do I do? I create a new

well. So what do I do? I create a new step right here. I just go ahead and start to write a function called table doc column names. So table dot column names I will start the bracket here is the name of the table which is source.

I'm going to close the bracket and press enter. And this produces a list of all

enter. And this produces a list of all the names of the columns. Now I will start to use the two functions that I spoke about which is list.t transform

and list dot select. I use that in a lot of variations and I will show that to you real quick. Now the list transform function what it does it can actually transform the items of the list. That

means it can go inside of this list go in every single row. Do something with the first item do something with the second item so on and so forth. The

transformation for every single item is customized. You can do anything but for

customized. You can do anything but for now let me just present to you an example. So let's just say that I'm

example. So let's just say that I'm going to go ahead and I'm going to say hey I want to check that every single word in the list contains the word student or not. That's what I would want

to check. So I'm going to say hey I want

to check. So I'm going to say hey I want to use the list.transform function and I'm going to start the bracket. Now list

transform as the first part asks you hey what's your list? So this is my list which which I can see right here. Then

in the second part of the list dot transform function you provide what is the transformation that you would want to do. So what do I want to do? I want

to do. So what do I want to do? I want

to check. So I'm going to say something like hey why don't you just check every single word each and underscore means every single word or every single item of the list check if that contains the text student or not. So I'm going to say

something like text contains I'm start the bracket and here is the very text and then does it contain the word student or not. So I will just write student and I'm just going to go close the bracket and close the bracket in the end and press enter. Now what the list

transform function has done is that it has actually given you the true or the false output. That means yes it contains

false output. That means yes it contains the word student. it contains the word student so on and so forth and that's what you have now I don't really want to do anything with the true and falses I rather want to keep the items which are

true that means I wanted student one column student two column three and four columns that's what I wanted now once you have done the transformation and you've gotten the trus into falses and you are able to see the result now you

can use those trs and falses to either keep the items or delete the items now if I happen to use the list dose select function with the same argument hey here is my list and here is a formula or a

function that generates a true and false necessarily. Now these true and falses

necessarily. Now these true and falses can be then be used to either pick up the item or lose the items from the list. If I now press enter, all the

list. If I now press enter, all the truths are kept and all the falses are removed. Now should I want to do

removed. Now should I want to do anything with student 1 2 3 4, I can now be able to do that. That is awesome. In

case you're liking the video thus far and you would want to be able to learn the M language deeply to be able to perform some really sophisticated data cleaning operations, I have a brilliant M course that teaches you the M language

which is the language behind Power Query's user interface. There are about 1,000 students in the course enjoying the course and I have some raving feedback about the course. In the

course, rather than actually help you mug up functions after the other, I lay a lot of focus on conceptual level understanding and application of problem solving. In case I'm able to help you

solving. In case I'm able to help you think how to structure the problem, you can then use that structured thinking to solve any kind of your own problems. After that, a lot of students have benefited from the course a lot. The

course is now available as a package inside the Goodly Insider community that I spoke at the start. There's also a Black Friday sale going on in case you're interested. You might just want

you're interested. You might just want to jump onto that. Let's get back to the video. All right, fellas. Let's just

video. All right, fellas. Let's just

pick it up from where we left in the previous one, which is where we were trying to extract the students right here and we want to do something with those columns. The next most obvious

those columns. The next most obvious thing after loading the data is something perhaps you want to do something with the columns. Maybe

transform the columns, clean the columns. Let's just take one such

columns. Let's just take one such example. So I've got these students

example. So I've got these students right here. And perhaps I want to

right here. And perhaps I want to capitalize the names of the students.

Now generally speaking, the function that does that using the user interface is the table.transform columns. And I

want to talk about the brilliance of that function if you know how to write that function yourself. Let me show that to you. So let's just say that all of

to you. So let's just say that all of these four columns that I have student 1 2 3 and four I want to convert them into uppercase. So how do I go about doing

uppercase. So how do I go about doing that using the user interface I'm actually going to pick up column one I'm going to pick up column number two I'm going to right click on it and I'm going to say transform and I'm just going to say hey why don't you convert that in the uppercase the formula that is

generated is something like this which is table.transform columns and I will

is table.transform columns and I will spend just a quick second to help you understand this function and then the brilliance and the magic of this function. All right, what I've done is

function. All right, what I've done is I've just copied the same function in the notepad right here so that I'm able to help you understand that how this function is made. I will just take two minutes on top of that. Now the very first part of the table.transform column

function is the name of the table and the name of the table is nothing but the previous tab which is nothing but source right here. Then if you notice it says

right here. Then if you notice it says that which all columns do you want to transform? So I said hey I want to

transform? So I said hey I want to transform student one column and then in the student one column I want to pick up every single value and I want to convert that to an uppercase. So table.upper

upper is a function and I also want to apply the data type which is nothing but the text and which is right here type as text and I can transform as many columns that I need and you can see that every single transformation that I'm doing is

in the form of a list and you can see that the first transformation that means student one column is one list you can see the curly brackets at the start a curly bracket in the end and student two column transformation is also a list a curly bracket at the start and a curly

bracket in the end that's the beauty about it and all of these lists which is the first column transformation the second column transformation information are then again packed inside an outer list. So it's like a nested list

list. So it's like a nested list structure. A list within the list. Every

structure. A list within the list. Every

single inside list is going to have three parts. The name of the column, the

three parts. The name of the column, the function and then the type of the text.

Now the type is not mandatory but hey we going to use that. Once we have understood this structure, can we automate this function in such a way that it automatically picks up student one and student two and student three and student four columns and we don't

have to manually select those columns.

Should the number of students columns expand or contract, this should be able to perform this operation of text.uppper

upper or text.proper on its own. Let's

just start to do that. To be able to begin to do this operation, what I'm going to do is I'm going to delete this particular part of text which is uh the upper transformation that I have applied and I'm just going to get back to that in just a quick second. Now, since I want to work with the student column

student 1 2 3 4 dynamically, I've already extracted that in the previous query and I'm just going to go ahead and copy that thing. So, I'm going to go right here to query number two and this particular formula that I wrote that automatically dynamically extracted all

the studentbased columns that had the word student. I'm going to copy this

word student. I'm going to copy this code. Come back to query number three.

code. Come back to query number three.

I'm just going to go ahead and create a new step. And I'm just going to paste my

new step. And I'm just going to paste my code right here so that I get the names of the columns. At this stage, I want to compare how the table.t transform

columns function to what I have at the moment. This is the result that I have

moment. This is the result that I have at the moment. And this is a single list. But the structure that I want is a

list. But the structure that I want is a nested list. That means this list is the

nested list. That means this list is the list which is outside that you can see right here. But every single item, that

right here. But every single item, that means the first item, the second item, the third item and the fourth item also need to be a list. That means we need a list here and we need a list here. Now

the list should have three items. Student one, the formula and the text which we will worry about in just a second. So what am I going to do? I'm

second. So what am I going to do? I'm

going to go ahead and start to transform this list. I'm going to say hey

this list. I'm going to say hey list.transform. I'm going to start the

list.transform. I'm going to start the bracket and I'm going to say hey every single item of the list right now just don't do anything with that. I'm just

going to close the bracket and press enter. And this does nothing at the

enter. And this does nothing at the moment. Let's just take a look at what

moment. Let's just take a look at what mistake did I do. There's an S missing here. Transform. And right now it works

here. Transform. And right now it works fine. Now what I want to do is since

fine. Now what I want to do is since every single item needs to be in the form of a list, I'm going to wrap the underscore in the curly brackets because the curly brackets actually mean a list.

And all that this will do is every single item which is student 1 2 3 4 will also be in the form of a list. And

if I go inside of the list, we just have one item in the list. If I get back to the comparison real quick, we have been able to solve this part. That means we have an outer list. That's done. We have

an inner list and we have four such lists. So this is a list, list, list and

lists. So this is a list, list, list and list. That's nice. But what we also need

list. That's nice. But what we also need to have within every single list is not one item but three items. The second item needs to be a formula which is text dot upper or text dot lower or whatever transformation and the data type right

here as well. So let's just add these two things. So what I'm going to do is

two things. So what I'm going to do is I'm going to go ahead and copy this particular part. Ctrl + C on that. And

particular part. Ctrl + C on that. And

I'm going to say hey in this list which is where we just have one item. I'm

going to add the second item which is going to be the text.per function. I do

that and I press enter. The list remains intact. But now the list has got two

intact. But now the list has got two items. The name of the column and the function which is text.upper upper or text dot lower. Now it shows you the text as function but it's actually a function. And then I'm just going to add

function. And then I'm just going to add the third item which is type text. So

I'm just going to say this is going to be a type text and we are sorted. Now

every single list has got three items just as the way we wanted. If you now compare what we have been able to generate is exactly like the way table.transform columns wanted it. Feed

table.transform columns wanted it. Feed

Power Query what it eats. It's one of the motos of my course and it's one of the motos of the book that I have written on Power Query. In case you haven't checked that out, please do check it out. Nevertheless, take a look at this. We wanted to have an outer

at this. We wanted to have an outer list. That's my outer list. We wanted to

list. That's my outer list. We wanted to have an inner list. That's my inner list. Every single inside list should

list. Every single inside list should have three parts. Name of the column, function or the formula, and then the type of that. Pretty cool. Now, we are all set. I'm just going to go ahead and

all set. I'm just going to go ahead and rename this particular thing. I'm just

going to call this as column list and press enter. And now we're going to go

press enter. And now we're going to go ahead and start to write the table.transform columns function. We've

table.transform columns function. We've

already done that function. So, I can just go ahead and copy that particular function. Ctrl + C on that. I can make

function. Ctrl + C on that. I can make in a new step and I can just feed that function that was manual to begin with.

Now you can see that of course student one and student two columns have been marked as text and the names also have been capitalized. That's nice but they

been capitalized. That's nice but they are manual. That means tomorrow if

are manual. That means tomorrow if student one changes or student two changes or there are more columns added it will not have that effect. Now this

part of the formula which is the nested list this part of the formula needs to be replaced with something that I have created which is more dynamic and that is nothing but the column list. How

beautiful is that? I'm just going to go ahead and say calls list and I'm just going to press enter. And as soon as I do that, you're going to see that student one, student two, student three, and student four all are uppercases with the data types applied. And that's the

beauty of this particular function.

Table.t transform columns. Now, I'm not done yet. The table.transform columns

done yet. The table.transform columns

function offers a host of capabilities of default transformation, specific transformation, uh, missing column transformation that as well. You have to learn all of that. I've done two videos in the past. I suggest that you take a look at those two videos. But for now,

let's just move on to my next trick.

After talking about list functions and table functions, my next favorite thing in Power Query is records. And I don't particularly like any one function in the records, but the sheer ability to

create records. Let me show that to you.

create records. Let me show that to you.

What do I mean? And how awesome is that?

Now, take a look. We still are working with the same data. And we have the max marks here. And perhaps I want to add

marks here. And perhaps I want to add two more columns. One is going to be the grace marks, which is 40% of the max marks. And the difference between grace

marks. And the difference between grace marks and max marks. Now this would mean that if you're doing it manually through the user interface, you would have to go to the add columns tab and then click on custom column twice. And if there were

four, five, seven columns, you would have to click that many number of times and you will obviously have those many number of steps right here. That's kind

of not good. So let's just do all of that using records and I'm going to show you a neat nifty trick with records. So

let's just go ahead and still go ahead and create an uh column right here. So

add columns. I'm going to create a custom column in the custom column.

Rather than actually creating the custom column in the conventional way, we're going to create the column in a modern way using records. How do you create a record? I'm just going to create a

record? I'm just going to create a square bracket and a square bracket to close the record and within which I can create data column names and the values.

The first column that I want to create is grace marks. So I'm just going to say grace marks. You can see that there is

grace marks. You can see that there is no need for writing any kind of quotation marks, any kind of square bracket. You can just specify the name

bracket. You can just specify the name of the column just the way it is unless you have some special character. All

right, grace marks good to go. I'm going

to say, hey, take the max marks and I can multiply the max marks with 40%. So

0.4 and that is going to give me my first column. Let's just say that I want

first column. Let's just say that I want to create my second column. So I'm just going to put in a comma and I'm going to say that the difference is going to be uh the grace marks that I have created minus the max marks. Actually the other way around. So I'm going to say hey max

way around. So I'm going to say hey max marks minus the grace marks is going to be the difference. I've made these two columns and I'm just going to click on okay. Now you can make as many number of

okay. Now you can make as many number of columns that you like all within a single record. I'm going to click on

single record. I'm going to click on okay. What I get is nothing but a custom

okay. What I get is nothing but a custom column within which I have a record.

Record inherently means one row of data.

If I peek into the records, I've got both these values which is nice. I can

click on expand. I can uncheck the name prefix. Click on okay. And I get both

prefix. Click on okay. And I get both the columns in one shot. Now obviously

we have two columns and one expanded step. So it doesn't really make a lot of

step. So it doesn't really make a lot of difference. But should you have three,

difference. But should you have three, four, five, six columns to create, you can all do that within one single step.

Now at the moment you're wondering that hey I still have to apply the data type to grace marks and the difference column which is a number. How do I do that within the record itself? Yes, let me show that to you how. So I'm going to go

back to the step which is where I added a record and this is the record that I added which is grace marks and difference. Now the feature of adding a

difference. Now the feature of adding a data type comes in this particular formula called table.add column. The

last part of this particular formula is to specify a data type. Now if you take a look at the custom column that we have created which is right here in this column we are not producing a number we not producing a text but what we are

producing is a record. So we are going to define a data type as a record and what columns do we want to specify. So

I'm just going to go in right here towards the last part of the formula put in a comma and that is where the column type or the data type is there. I'm

going to say that I am trying to define a type which is nothing but a record. So

I'll specify a record that means start and close square brackets and I will literally mention the names of the columns that I had which is nothing but grace marks C on that control + V on that and I'm going to say that this is should be equal to a type and that is

nothing but a number. Then I'm going to mention the other column which is nothing but difference and that would also be equal to a number and that's pretty much it. Once I actually press enter on this nothing changes you can see that the data type is nothing but a

record but the record actually contains the data types of the columns that you have created which is grace marks and difference. Make sure the spelling is

difference. Make sure the spelling is just the same otherwise the column types are not going to be applied. It will not error out but the column types are not going to be applied. Once you expand these columns in the next step you're going to see that the data types are

also applied which is a number and a number right here. That's the magic of records. Another very common operation

records. Another very common operation in Power Query is to remove the junk rows from the top. Sometimes you're

going to have static junk rows.

Sometimes you're going to have non-static junk rows. That means you don't know how many junk rows are going to be there on the top. You have to look at the data and figure that out. Let me

show that to you in an example. So I've

got this data, the same data by the way, and we've got three junk rows on the top, the first row, the second row, the third row. But at the moment, I am kind

third row. But at the moment, I am kind of in this puzzle where I can't say, "Hey, go to the home tab." And I have something called remove rows up on the top. So remove rows, remove top rows,

top. So remove rows, remove top rows, and I'm just can't say three here.

Although this is actually going to solve the problem using the table.skip

function. And it removed the top three rows of the table. But I don't know the next table or the next query I will have that number static or not. So I want to make it dynamic. The cool thing that I want to talk about the table.skip

function is that you can actually go ahead and write a count which is three or you can also write a condition. And

I'm going to teach you how do you write a condition. Let's just formulate the

a condition. Let's just formulate the logic first. I'm going to go ahead and

logic first. I'm going to go ahead and delete this step and let's just formulate the logic. The logic is very very simple. My assumption is that let's

very simple. My assumption is that let's just say even if I do not know what the headers of the data are, I can assume safely that a junk row will not have all the rows filled. That means a junk value

will not be filled here, will not be filled here or in all of the columns.

That means either one or a few columns are going to be empty and they are going to be contained in let's say column 1 or column 2. That's what I mean. So the

column 2. That's what I mean. So the

logic is something like this. All that I want to check is that if this particular first row has any value equal to null then it's a junk row. If the second row has any value equal to null then it's a

junk row. And only when I have proper

junk row. And only when I have proper headers none of the values are going to be null and that is where I want to stop skipping the rows. How do you write all of that? I'm going to show that to you.

of that? I'm going to show that to you.

So we're going to use some user interface to be able to understand. And

then we will use the table.skip function

to be able to translate that into a condition. So I'm going to go ahead and

condition. So I'm going to go ahead and add a column so that you can visually see it and create a custom column. For

the moment I'm going to write an underscore. You will just understand the

underscore. You will just understand the underscore in just a quick second but allow me to proceed. I write the underscore. I click on okay because the

underscore. I click on okay because the underscore was used within a table and the underscore means the current item.

So the current item in the first row is the first row. Row means a record.

That's where you have a record. In the

second row you have the second row or the second record. Record record. Re

record. It means the current item of the table. So if you peek into this, this

table. So if you peek into this, this actually gives you whatever values are there in the first row. These are all the values of the first row, all the values of the second row, so on and so forth. And all I want to check is that

forth. And all I want to check is that any of these values is equal to a null or not. Let's just start. I'm going to

or not. Let's just start. I'm going to go ahead and start to write some condition so that I'm able to work only with the values and not the headers. I'm

not concerned with what the headers are.

That means this is useless for me. And I

just want to work with these values only. I want to strip away the headers.

only. I want to strip away the headers.

How do I do that? It happens to be a nice function called record do. field

values. Let's just go use that function.

So I'm going to say that hey the underscore is producing a record and I want to pick up the values of the record. So record dot field values. I

record. So record dot field values. I

will start the bracket and close the bracket at the end. I'm going to click on okay. And this actually produces a

on okay. And this actually produces a list. The list has removed the headers

list. The list has removed the headers of the columns and all that we are remaining with are actual values. Now in

this list the only thing for me to check is that do you have a null? Do you

contain a null or not? Simple as that.

So I can just go ahead and write the formula or revise my formula. Does this

list contain a null value or not? So I

can use the function called list.contain. contains here is my list

list.contain. contains here is my list and does it contain a null value or not?

I can just close the bracket. This is

actually going to give me a true and false and I'm good with that. Now, here

is how the table.s skip function is going to work. We've almost gotten to the answer. I want to give you the last

the answer. I want to give you the last piece of logic. The table.s skip

function will keep the rows till the time it finds the true. As soon as it finds the first false, it'll stop to skip the rows. Remember, it works on the false condition, not the true condition.

So, is the first row meeting the condition? Do you want to skip it? True,

condition? Do you want to skip it? True,

skip it. True, skip it. True, skip it, false, do not skip. That's what the logic is. Now, how do I take this logic

logic is. Now, how do I take this logic and feed it into tablets skip function?

What I can do is I can go ahead and take this part of the formula, which is all of what I have written in the custom formula. Can take all of that, Ctrl + C

formula. Can take all of that, Ctrl + C on that, and I can just go cancel. I can

delete the step. I am done with the logic for every single row. I can feed that logic in the table.s skip. Let's

just get back the table.s skip function.

So, I'm just going to go over to the home tab, say remove rows, remove the top rows. For now, I will feed the

top rows. For now, I will feed the manual value as three. Click on okay.

And this three will now be replaced with the formula that I wrote which is the list contains. Close the bracket in the

list contains. Close the bracket in the end. Press enter. I think there is going

end. Press enter. I think there is going to be an each as well here because it needs to go in every single row. I will

press enter and nothing changes but your output is just like that. Now obviously

you can promote the headers of the data and you are actually good to go. If you

haven't been able to find the secret word, I suggest that you watch the video carefully once again take a look at the entire video package and you should be able to find the word. If you mention the word in the comments in the next 7

days after the video goes out. I am

going to give one of you a free goodly insider subscription for the next 12 months. In case you'd like to learn

months. In case you'd like to learn about very interesting DAX functions, there's another video that you would want to watch. Otherwise, I'm going to see you inside the community. Cheers.

[music] >> [music]