Power Query - Split Table Dynamically

greeting salutations it's me James your ba sense back with another Power cre tutorial the first one for 2024 the year of the dragon and today's video I'm going to show you two things I'm going to show you how to use a table split ad function and I'm going to show you a nice use case for nested Le functions all right so the data set we're going to be looking at is this pivoted stock portfolio over here you can see we basically have the stock portfolio as at 2022 pivoted in a pivoted format and then we add the same stock portfolio 2023 so we want to use the split function to split this one data set into two and then basically un pivoted and then pivoted it back so we have Dynamic column titles for each quarter over year okay so enough speaking let me show you how to do it all right all right let's get the data into Power queries go to data get data from file Excel workbook select the file you can see this data all comes in one sheet and there you can see it's actually two data sets rolled into one I say transform data now pqu opens I don't like this change data type thing here because it hard codes the the titles I say no I don't want to promote the Ed I delete that navigation is cool we'd leave it at that so this is our data set let's rename this to split demo yes first thing I'm going to do is I'm going to add a index column starting at one all right the second thing I want to do is because I'm use to split at function one of the perimeters of the split at function is where do you want to split the table and I obviously want to split it where this value is null but I don't want to hardcode it at line 26 because it might be different for whever I use it so I want to dynamically determine what position this number 26 is at so what I'm going to do is I'm going to start a new Step let's call this step split location yes and what we do in there is we simply go table select rows because you want to find the row in the ad index table which is this table and and I want to say each and I want to find where column one is that First Column is equal to null that should give me that as you can see it's row 26 I don't want that as an answer so what I want is I want to say give me the index column cool it gives me that but that's not good enough I just want a single value so I'm just going to say curly bracket zero and that's going to give me a number 2 and you can see that actually means at row 26 that's where we want to split it so split location is that place where we want to dynamically split all right so let's add a new Step I'm going to refer back to added index before that step now I'm going to use the split at function so I'm going to say table split at yes it takes two perimeters take the table and where we want to split it luckily we say the table is the added index table and we know where we splitted it we we know where we should split it that's at row 26 and we created that variable called split location and we say close the bracket whoa and now we have the first table you can see table one yes and table two split exactly from that null so that's a dynamic splitter which is so excellent all right so note one thing this table split at basically created a list with a table two tables inside of it all right so it's actually a list we're going to work with let's quickly rename this custom to Splat all right so we're going to say add a new Step going to click on table there list transform and we're going to give it the custom one list which is the previous if you can see is the list of the tables give it as an input and now we want to apply our clustered Le so I'm going to say each I'm going to press shift and enter first thing I want to do is I want to remove this index column I really don't need that so I'm going to say table remove columns and I want to tell this table is the underscore that refers to the table within each list and I want to remove the index column before we do that let's say do the clustered let let let's give this thing a name let's call this to a and we say in a this is the Syntax for a cluster plate and I say okay so what it did now is it went into each of these tables in this list and it removed the index column pretty neat pretty neat next thing I want to do is I want to promote the headers so I'm just going to add a step to this clustered let comma after the a I'm going to say B we say table promote headers and the input to that the table would be a which is the previous variable in the let statement a and that's it I want to promote that and you just need to remember the N needs to now refer to the latest variable which is B and I say okay look at the table now the headers have been promoted isn't that excellent okay so the next thing I want to do is I want to unpivot these data sets I want to have the stock code as the rows and I want to unpivot these all right so what we do is we're going to add another step in there we're going to say C inside our clustered let we going to say table UNP other columns and the table we're going to give it is table B which is the previous variable and I'm going to tell it I want to pivoted the rows I want to keep it at stock which is that First Column over there that's the one row I want to keep in my Pivot I want to create a new column called period name and the value should go into amount okay and I need to remember to update the in to see and that should do it let's quickly look and like magic it unpivot our data set pretty cool Next Step I'm going to add another step and I want to take these two data sets because you can see the column names are now uniform I want to now table combined table combine give it the input of the table yes there we go we've basically done it last thing that I want to do is I want to take the amount column and I want to transform that to uh let's call that a whole number this all number of stocks yeah yes and I'm going to select the period name the amount I want to go to Pivot the column the values are contained within the amount and I say all right there we go there we go and let's return this to Excel and there you go well I hope that opened your mind how you can use clustered lets and also use the split at function well ba Sensei signing out

Channel: BA Sensei

Views: 1,693

Keywords:

Id: FB5Zs29A8fw

Length: 6min 53sec (413 seconds)

Published: Sun Jan 21 2024