The Magic of working with Records in Power Query

first off it's new year so we wish you a very very happy New Year a lot of Happiness may come to you this year and of course alongside happiness you may also get faster refreshes and less errors in your power bi models now starting our journey with power query this year I have to talk about records just a few weeks ago I talked about lists people like the idea of understanding lists right from scratch and I draw up a few applications of lists as well in this video I'm going to talk about records and how records are super helpful in carrying out some sophisticated operations in power bi Bible query all right you and I record let's start [Music] let's just first talk about some fundamentals of understanding records and then we'll try to draw up some very interesting applications of Records so what exactly is a record record is nothing but a row of a table that's what a recorded one single row of any particular table is nothing but a record so you can see that this particular table which I have it as an example has got four records record one record two record three and record four now the thing that you have to understand records which is really really important records do come in pairs that means it's not just the first row of the data but it's also the header of that particular column so the record is going to come in two pairs the value of the first row of every single column alongside the headers of those columns as well so the record is going to contain two things the headers as well as the value so in the second record or the third record you're going to have the value of the third row of the data and alongside you're also going to have the headers the next thing that we'd like to talk about is that in case you you'd like to extract a record from a particular table how do you do that then we'll talk about that in case you want to create a record from scratch how can you do that so let's just start with extracting a record so here is a table right here and maybe I'd like to extract the second record right here which is row number two now what I'm going to do is I'm going to maybe go ahead and create a new step right here source is the name of the previous step which is nothing but my table and from their table I'd like to get the second row right here to be able to extract a record of the table I will use the curly brackets so name of the table in the curly brackets which row do I want I want the second row of the table I press enter and what I have got is the second record or the second row of the table now if you take a look there are two peculiar things to point out the first one is that the record haven't really appeared in a row format like it was supposed to be a row but it appears in a columnar format although it is still a row of the data that's how it shows up in power query that's one thing to understand the second thing that I also talked about earlier was that that record is going to have two parts it's gonna have the values which is Tommy Jan Feb March and April and of course the headers right here so it comes in pairs of two all right now that we have understood that how do you extract a record so you have the name of the table and if you just mention the row that you would like to extract you can extract the record off the table now let's just say that in case you want to create a record from scratch how do you do that so I'm gonna maybe not really write the table name and the row number here but I'm going to start to create a record in order for us to create a record what you would need is initiate the record in a square bracket and that's what we're going to use so I'm going to write the square bracket in the square bracket I'm going to start to mention two things the name of the column and the value of the column as many columns as I like but the row should be just one single row so how do I do that let's just say that the First Column is going to be the name column no um double quotes anything like that name of the column just appears without anything so name of the column let's just say is let's say my name and then we'll talk about maybe another column called City uh which is column number two and currently I'm in Dubai so that's the city and we'll talk about let's say a number so we'll just write number and let's say 100 is any random number now this is one row of data with three different columns and the three values underneath if I now commit to this you're gonna see that we have been able to create a record and that's as easy as that now with that understanding built let's just talk about some very interesting applications of those records that what can you do with records in Practical cases that you have to solve in your daily life how records are helpful let's just get on with that all right in the first example we are working with the same data here a name and a couple of month columns here and this time the need is that I'd like to sum all the months and add a new column right here on the right hand side now this typically would require you to unpivot the data and then take a total and then probably do something and then pivot it back but then with the help of the records we are going to just omit all of that task and just maybe use records to help us do that thing so I'm going to create a new column here and create a custom column and I'm going to write an underscore right here now if you don't really know what does the underscore mean in this particular context I highly recommend that you take a look at the video that I have done on understanding the each and the underscore and then you'll probably understand what I'm trying to do here is I'm just trying to fetch every single row right here so this is the first row which is the first record the second row the second record third record fourth record that's what I'm trying to do now as soon as I commit to this particular function that I'm writing click on OK I'm going to get a record if I peek into that record I'm gonna see all the values of of the column so I have of course the names of the columns and I have the values of those columns as well now what I would want to do is I don't really want to do anything with the names of the columns so maybe I'll have to get rid of that that's part one and I also want to get rid of the name here because a text value cannot be total so how do I do all of that what I'm going to do is I'm going to morph the record into a form of a list that means I have been able to extract the record but I don't really want the record anymore I just want the values of that particular record so I'm going to go back to my function in the making and I'm going to start writing a function that converts the record into a list and that function is nothing but record dot to list and I'm just going to maybe write the underscore which gives me the record and record.2 list converts that record into a list let's just see what do we get now now I get a list and the names of the columns have been gone because list doesn't contain any column headers and we have the values now the thing that I want to get rid of is the first value right here and then sum all of these remaining values how do you do that I'm going to go back to my function right here and I'm going to start to create something like list dot skip I'd like to skip the first value of the list which is nothing but the text value and for the remaining values that remain I'm gonna maybe use the simple function called list dot sum which is going to maybe take a total of all the values which are remaining which are numbers right and I'm going to call this as sum we are good to go and I'm going to commit and that is my sum pretty easy right now let's just move to example number two which is an even more interesting application of working with records if you don't already know this this is going to blow your minds out so please take a look working with a simple query light here which has got a couple of steps pretty much the same query Source I unpivot the months then I rename the columns as month and units pretty standard stuff change the type and then I have grouped the rows and I've got the totals against all the names right here pretty standard stuff nothing that complicated now somebody asks you a very very weird thing or maybe you come across a very weird requirement in your process of working with power query what you need so you would want to extract the step of the query that means you would want to extract any particular steps of the five steps that you have done sure enough you are able to get the last step referenced to any other query but you can't really extract a step of like which is not the last step of the query I'm going to teach you how to do that using records so what we're going to do is we're going to go back to the advanced editor so in the view tab I go over to the advanced editor and this entire query that we have which is a five step query I'm going to convert that into a record how do you do that now remember that records are going to have two parts the name of the column and the value of the column right all right so I'm going to get rid of the let statement delete that and start the square brackets because records are declared in square brackets that's what I told you and I'm going to get rid of the in statement and the ending part group rows right here and I'm going to close the uh my record right here so start of the record close off the Record now if you pay attention the record satisfies the need which is it should have name of the column and it should have the value of the column the only difference is that the value of the column here is not really a like a single scale or value it's sometimes a table sometimes it's unpivot operations and things like that but it still satisfies the need now if I just maybe convert that into a record and click on done what I'm going to get is the steps are just gone right here and all of those steps are now converted into records right so this is the source table the unpivoted data the rename columns change type group rows so on and so forth right now what we do is we create another query and we would now be able to fetch any particular step right here so I'm going to click on other sources click on blank query and I'm going to reference back to example two which is my query in the making so example two now example two has got multiple records if I now press enter it's going to give me all the five records which are nothing but the steps now I can fetch anything so maybe what I can do is I can just maybe fetch the last step or maybe the third last step which is let's say the rename column so renamed columns I hope I'm writing that correctly and that just works beautifully now I have been able to extract the third step of this particular query and this is just using record so in case you are designing any sophisticated query and you would want to extract an interim step of the query records are a great way to do that all right that's when it that was the start of a video of understanding records and a couple of applications to help you get started and utilizing records to build more sophisticated queries in case you have any questions around this feel free to drop in a comment and I will be glad to reply in the end a big shout about my tax and my power query courses in case you are a beginner in power bi and you'd like to master the fundamentals really really well and then also solve more challenging more sophisticated problems even of your own data I'd highly recommend that you take a look at my courses it is going to be super awesome thanks so much for sticking all around and I will catch you guys in the next one cheers and bye now [Music]

Channel: Goodly

Views: 31,910

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: Jr78-Dc34ic

Length: 10min 28sec (628 seconds)

Published: Mon Jan 02 2023