Dynamically expand table column in Power Query (advanced)

[Music] uh hello in this video we're going to show you a more advanced technique on expanding dynamic column names in our previous example we showed how we can easily create a dynamic function that is expanding column names so for instance we have these three tables and if we connect them through a different excel so first let's close this free table this excel and now let's open a blank one and we will connect to this workbook that was previously opened let's go to desktop and let's go to source file so here we have our three tables and we will choose to transform data i will just sort this sending we want only tables so we will remove a sheet kind and we are interested only in this two columns so in our last video we showed how we can create a dynamic table expansion so we have to drill into the first table and then we have to collect its column names so we'll use this for that we'll use table dot column names function and this will create a list of all the column names and then we have to copy this code remove this step and expand the previous step and then instead of this hard coded values we have to paste our code so this way we get a dynamic expanding list and this works okay so in case we were to open our excel file in case you wish to open an additional excel while you are inside of power query you have to hold alt button and then you can right click on this icon excel icon and you can open a new instance of excel and then from that instance you can open another excel file while still being inside of a power query in other file so we opened our source file and for instance if i add new column let's call it type and let's add some values in it let's save this close and if we head back to our power query and we click on refresh we immediately get this new column appended or added to to the table what is the shortcoming of uh this technique the shortcoming is that in case uh it is only accessing the names the column names from the first nested table so in case we open our excel again our source file and then we add a column a new column in a different table than the one that is first so let's add a new column name let's call this type 2 and let's add another let's say add new dummy figures and save it and then close it in this case if we refresh nothing happens why because we have this new column name nested in the second table but the expanded data is using only the first table the first nested table to create a list of names so how to overcome this issue we will explain it in the following following minutes so first let's remove this expanded data step and the thing that you want to do we want to first add new column let's add a custom column and let's call this column of column names names and then we will use a formula bar to invoke table dot column names function on each row or or or on each table in this column and let's click on ok we will receive a list a new column that is holding a lists that are holding all the column names from all the tables now we have to expand this to a new to new rows and now we get expanded column names we we are only interested in the column names column so we can copy this and we can access only that column by including that name in the square brackets of the step so now we get the the list and last thing that we need to do we need to remove duplicates to create a list that will be used to expand our column maps but this list holds all the column names of all the nested tables uh the next thing that we can do let's go to advanced theater we will create we will show you how to create a nested environment so we can we can move forward with this type of steps but to create a clear code we will show you how to create a nested or inner environment inside of an outer lat environment so we'll focus only on the steps that we use to extract a list of distinct column names and we started doing that after the removed other column step so we will add a new step called all column names and we will say that this is a inner let environment and we will say that these three steps are the steps that are bound to this inner environment and also the inner environment has to have let the steps applied to that environment and then in as an exit step of the environment and now if we add this as a last as the last step or the exit step of the outer environment if you click on done will receive only a single step and this step holds the column names or the the distinct column names and inside if we open the formula bar we see that this is actually a nested or inner environment holding the three steps used to create this list moving forward so now we have to expand this data just to get the code so let's go with insert okay we will move this below and now we have we have this expanded data which is holding the the formula and the thing is it cannot access or reference all column names step because this is a list it has to reference the previous step or the removed other column step so instead of all column names we will reference removed other column step and instead of this list will now include all column names variable that is holding the unique distinct names of the all the columns in every table so after we paste this this uh this variable two times we get a table that is expanded by but it uses all the columns from all the tables in doing this operation so if we were to open again our source file and if we add let's say three new columns to each table let's go this column a let's call this column b and let's call this column c if we save this we close it and now we go to refresh we'll see that we have all three columns dynamically expanded in our table so i hope you enjoyed this video and you found it useful we talked about lists we talked about environments and we talked about how to skip steps in script and how to create this dynamic list of expanding names hope you enjoyed it and if you did hit like subscribe button and stay tuned for more see you in the next video bye you

Channel: Exceed Learning

Views: 9,297

Keywords: power, query, powerquery, dynamic, column, expansion, expand, table, powerbi

Id: 80LNSSjesjw

Length: 9min 51sec (591 seconds)

Published: Thu Feb 11 2021