Power Query - Advanced Transformation of Nested Tables

greetings salutations it's me James your ba Sensei welcome back to another Power quer tutorial today we're going to be doing some excellent things in M query where we are going to transform nested tables and the context of what we're going to do we are going to take a folder with some portfolios in it we're going to draw data from this folder and inside we got various portfolios you can see the sheet uh is the portfolio name and there's the stock portfolio at that period there's the other one uh portfolio name portfolio name and you can see they all have different spacing and the last one you can see okay a lot of stocks in there and also same thing um it's not all standard so what we're going to do is we're going to use nested let functions and transform these nested tables enough talking let me show you how to do it okay so let's start with the fresh XL workbook over here so in your conventional UI way what you'll be doing is you'll go to data get data from file from folder you select the foldo location but I'm not going to do that because I'm going to try and show you how to use M query a little bit better and understand nested tables and transforming nested tables a little bit more advanced than normal so what we're going to is we're going to say other sources blank query opens power query over here and we just go to Advanced editor I'm just going to take my folder locations copy it out there go into my Advanced edit and just paste it in over there my query name there and say folder Source cool we're going to use this in our actual query so let's start a new query over there other source blank query let's call this solution okay so in the source we just going to say folder files and we want to pull it from our folder source that we just created now and that should give you something like this pretty cool so this kind of gives you a peak inside of the folder and all the files within that folder let's add a step here and we say start like square brackets two of them you say bring me the content and bring me the name you can see now I've selected those two I'm just going to rename this to press f2 select columns yes now what I want to do next is I want to transform this binary into a a nested table I'm now going to show you how to use table transform columns to achieve this we're going to say add a new Step table transform columns yes and give it that previous table name select columns yes the Syntax for transform columns I love transform columns it's a very powerful function I use it everywhere okay so I'm going to show you so now you need to declare a list yes the transformation operation as a list and we want to transform the content column and I want to push it through the function Excel workbook Excel workbook and just put that little underscore there that refers to the binary in that file and you say okay and you can see this transformed into a table you can see there's a table giving us a peak what's going on inside of each of those spreadsheets okay just one thing I want to do here I want to you can see we are some cases here where there's tables I want to filter out the tables I only want the sheets all right so I kind of want to do that now so I'm going to expand on this a little bit I'm going to take this existing one over here and just say after this each just press uh shift and enter I'm going to say table select rows yes and I want to look specifically in the Excel workbook obviously and in there I'm going to say for each item in there I'm going to say each column and I'm looking at the kind column I want that to be sheet and Sheet only I don't want to have anything else and here you can see we basically filtered out only the sheets I'm going to call this content that's the content there we go now simply I can use a function but I'm just going to press expand and all I really want is I want the name and I want the actual data and now we have your portfolio name and you got your file name and this is our nested table we're going to transform we're going to use nested lets and trans transform columns to do some magic in here let's rename this to expanded I'm just going to press new Step just click there so we can see the contents of the table we say table transform columns and we give it the table as a input and for this we need to get a transform operation as a list and we first going to refer to first thing I want to remove there is I want to get rid I want to First identify where my data set start it needs to start at stock Okay cool so we're going to refer to expand it I'm going to say I want to look at the data column yes and for let just press shift and enter and for each element in the data table now we're in this child nested table I'm going to say start square brackets yes and in there I'm going to declare a variable let's call this find stock uh to basically find where my table starts and I'm going to say table Skip and we're going to give it our underscore which refers to the table and I'm going to say each for each row with this n table I want to look at column one yes where column one is not equal to stock because that's the pattern we have here so basically I know my table starts at stock so anything that's not stock that's not really anything that we're interested in and all we need to do is after this curly bracket after the block bracket I just need to put the find stock name am in there and I need to close this off like that so now what you'll see is we basically got rid we use the table skip to basically skip all the top rows to get to a nice clean table pretty cool next thing we need to do is I want to promote the headers of the table okay so let's add a step so go there put a comma in there say promote and for this I'll say table promote headers and I'm I'm just going to give it the fine stock on my previous step and you just need to remember to replace that fine stock over there with promote and then like magic you have promoted your headers next thing that I don't like over here as you can see I got a null Row in there for the totals so let's quickly exclude that so go back comma and we're going to do another Nest thing here let's say exclude totals and we're going to say table select rows yes I'm going to give it the promote as the input that's the previous table I'm going to say each stock column inside the N table null all right and I'm just going to say exclude totals going slap that in there and let's see if there's any nulls in there no NS we've removed it I want to unpivot this data set so let's say over there go back to the previous function let's say unpivot and then here I'm just going to say table unpivot other columns and I'm going to take the exclude totals as the table that we're referring to and I'm going to say I want to keep the rows the stock code as the rows as you can see there's the stock code but what I want to do is I want to have a column for period which is that quarter period and and I want to that's not in curly brackets just remove the curly brackets and I want one for the Holdings the actual values yeah and that's as simple as that all I do is I need to return the unpivot in there and like magic there we go we've unived the data set so now we're going to do something really cool so what you could do right now is you can just click on there and say expand this is the conventional way and then you go like oh rename this the portfolio Nam it to source and you got what you wanted but I don't want to do that I want to explain to you how you can use inner and outer context for tables to take the the data of the outside table and insert it into the inside table so I want to take this name and this name and insert it over there so how would I do that okay so I'm going to make it a little bit harder I'm not going to take the easy way out just going to say add a new Step before we do that just click here so we can have a little preview what's going on there so we say table add column yes enter the custom yeah that's the previous one we're going to add a whole new column at the I'm going to add a whole new column over there called Final Table yes and in there I'm going to say each and now I'm going to use a nested let statement all right so I'm going to say let let's call this um add inner add to Inner okay this is variable name that could be anything that you want to give it if you want to call this XYZ you could I'm just going to call it XYZ then okay so in context of that we're going to add a new column there but what I want to do is I want to say table add a column to the inner table okay I want to add a column to the inner table The Nest table as well I want to add that to the data data column yes you can see this inner table and I want to call call this new column the portfolio and source file yeah but you see the problem now is I can't use each statement if I want to go from the outer table put something from an outer table to Inner table I need to use a function so this is what I need to do I need to say I'm just going to say x you can give it any old name I'm now going to do a inner and outer table so because we this is the outer table and this is the inner table so I'm going to take a variable from the outer table and put it into the inner table I'm going to say take name one from the outer table yes and let's just put a pipe delimiter in there and say and and we're going to join it to name okay and I'm going to say this is in and let's just give it XY Z need to close it off so okay so you can see now it added that new thing called new table final table and you can see what we did is we added in the inner table we took two elements from the outer table and added it to the inner table that's pretty cool so all I want to do now is this XYZ variable that I created you can literally call it anything I now want to do a little split and just split it by that pipy limiter so all I'm going to say there at the end going say table split column yes I'm going to give it that XYZ as an input and we're going to split portfolio and source that portfolio and Source column yes and we're going to split it by splitter split text by the limiter and I'm going to say we're going to use the pipd limiter and I want to return I want to call these two new columns portfolio and source file let's see what happened there we go pretty cool once again it's a long way of doing it but now you can kind of like see how to join an outer table to an inner table let's add a step because this final table now contains everything that we want to know what what we need so we can just say table combine give it that and we just going to say we only need the final like that and there you go there you go now I can return it to Excel and there you have it so now that you've seen that there's a lot of easy ways to just do the exact same thing by just expanding the tables and all of that but I hope just gave you some idea of how you can transform nested tables using table transform columns and then also referring an outer to an inner table it's pretty powerful stuff anyway ba says a signing out

Channel: BA Sensei

Views: 1,454

Keywords:

Id: e3c142fGJnQ

Length: 12min 37sec (757 seconds)

Published: Fri Feb 02 2024