Dynamically Expand All Columns from Multiple Tables in Power Query

hi you're watching the bad goodly once again and in this video i'm going to talk about how do you dynamically expand the columns especially when you're trying to combine the data from multiple excel files now these could be multiple excel files these could be multiple sheets from a single excel file or multiple tables just in general but we'll see that how do you dynamically expand the columns to be able to clearly understand the problem let's just run into the problem and then let's just try to solve that up all right so i'm the excel file in this excel file i have three sheets and each of the sheets contain the same structure of the data the columns are absolutely the same so sheet number one contains the data for 2005 sheet number two contains the data for 2006 and sheet number three contains the data for 2007. when i'm trying to combine the data of all the three sheets merge together and bring it together in a single excel file using power query what will happen is that just in case if i add another column to any of these three sheets the column will not automatically show up in power query let's just create a query to combine the data first and let's just kind of take a look at the problem okay so i'm here in a blank excel workbook and i will try to combine the data from the three sheets that i saw earlier so i'll go to data tab in the data tab i'll click on get data from file from the workbook and it shows me that excel file multiple years i'm just going to click on import and i get to see all the three sheets there are in that particular excel file now i don't really want to navigate to a single sheet i want to work with the entire workbook and combine the data so i'm just going to click on the folder right here click on transform data and i land up in power query which is where i can take a look at all the three sheets all right so you can see that we have a name column here we have the data column here in the data column we have the three tables these three tables are nothing but the individual data of every single sheet and a couple of other columns right here now in order to combine the data i just really want to work with this particular column which has the data right click on this column remove other columns that i don't need i will click on expand and it just shows me all the columns there are six columns the data structure is the same i'm just gonna click on ok and the data gets combined from all the sheets cool enough now you can see that one of the problem is that the data header has come in the first row it should not be in the first row it should actually be promoted to the header and i can just click on the home tab in the home tab i can just click on use the first row as headers and that becomes the header in the transform tab i can also detect data type so right select all the columns detect the data type and every column has its own data type correctly applied now the problem is that since the headers came into the first row so would have been the case for every single sheet now in this column somewhere you would find a word date and date cannot be converted to an actual date as a data type so it kind of shows me an error here but certainly i don't really want that so i can just click here and i can get rid of any errors so remove errors this data set is good to go i can click on the home tab close and load and the entire data gets combined put into my excel file now the problem is if i go back to my excel file which contained the data and in one of the three sheets maybe on sheet number three i add a new column so just say new column and i just write the value a across all the rows of the data save the excel file come back to my query and if i hit a refresh on the query i will not really get to see that new column now let's just go investigate the reason for that so i'm just going to click on the multiple years excel file the query right here and let's just start to investigate the steps that we have created so we were on the source step we removed the column so far so good and then we expanded the data now when the data got expanded the column names the six columns that you had they got hard coded in the formula so column one comma column two column three so on and so forth now unless manually you end up writing column seven here this is not going to change alternatively what you can also do is you can click on the gear icon and in the gear icon maybe you can say that i want to take a look at the addition column that has got just got added which is column number seven you can also click on load more to see that column and then only after you check on that particular column you will get to see the new column that has been added now that is a bit of manual work that you'll have to do every single time a new column has been added into the source data set now what can we do to our query to just get rid of that manual work and this should automatically be added into the query and the result should be there in my excel all right so what i'll do is to be able to get to the answer i will start to modify my same query and i will get to the solution so first of all let's just delete all these steps that we have done and write a slightly different query right from the source step so when we are on the source step we can take a look at all the columns along with the data column which contains the data now one of the problems with this particular column is that the header is in the first row of the data it's not really promoted to the header row what i would want to do is i want to promote the headers before i do any kind of work with this particular data set now if your headers are already promoted you would not do what i'm just doing as of now so in the add columns tab i will come to custom column and i'll write a new formula called table dot remote headers and table dot remote headers actually accepts a table as an input so you can see that it's asking you for a table for which table should i go and promote the header so i'm going to say hey why don't you promote the header for this table for this table and for this table and all of the three tables are there in the data column i'm just going to write the name of the column data that is where this formula is going to find three tables and it will promote the headers i'm just going to close the bracket say ok and i again get three tables now the difference between these three tables and these three tables is that the headers are not promoted and here the headers are promoted to the correct place good to go now let's just kind of delete all the other columns and let's just work with the custom columns right click here remove other columns and we have all the three tables with us now the way generally people combine the data from multiple tables is by clicking on the expand button and then the data gets combined now that's not what we're going to do we're going to create a new step here in the new step we're going to use the function called table dot combine so i'm just going to write a function here table dot combine and it just refers back to the previous steps remove other columns is nothing but the name of the previous step if i just kind of go back to the previous step remove other columns is the step which gives me the entire table in that entire table i just have one single column with three tables inside of that but as of now this is kind of giving me an error because table dot combine is not going to accept a full table it's going to accept a list and the list should have multiple tables so this is not really a list this is a full table but we want to convert this into a list with three tables so what i can do is i can refer back to the previous step which is a table and let's just convert that to a list by referring to a single column what is a list list is nothing but table with just one column is a list so in the previous step i will just refer to a single column which is the only column in that particular step which is nothing but custom and i'll close the bracket close the bracket right here this column has multiple tables that i want to combine i'm just going to hit enter and all the columns get combined and i can also see the new column that has been added now certainly i can maybe click on all the columns go to the transform tab detect data type to apply the data type i'm just going to come to the home tab say close and apply and we are kind of good to go and you can see that in the query the new column gets added but let's just really test this out so i'm going to go back to my excel and this time maybe on sheet number 2 i'll add a new column completely so i will say that a category column and write category number one across all the rows here i will save this excel file come back to my query right here and i will right click and refresh and i should expect to see a new category column being added here so right click and refresh and i get to see the new category column being added automatically so that's another way of combining data from multiple excel files and also dynamically adding columns just in case you want to have any columns being captured automatically all right so this solution is good enough but there is a slight twist and a problem to the solution what if you wanted to see the name of the sheet along with the data that gets combined from every single sheet so if i maybe just click here i don't have any column that is actually telling me the name of the sheet let's just go investigate our query as to see what's happening here so if i just go and take a look at every single step of the query i went to the source step then i added a custom column to promote the headers and then i removed all the columns that means i actually got rid of the name of the sheet and i just kept this column and since i just kept this particular column that's the only column and that's the only columns data that i end up getting now we need to modify the query slightly to be able to get any of the previous columns in the previous steps along with the columns dynamically being expanded added to the entire data set that gets loaded into my power query so what do i do i end up deleting all these steps i delete that i delete that and i delete that now the only step here is the data and the tables are being promoted as headers right here and we have the promoted headers table now what i'm going to do is first of all i will create a new step here which is where i will try to get all the possible headers there are in the three tables so i'm going to create a new step here and just like the way that i did it in the previous thing here so i'm just going to use the same function table dot combine and i will say hey in the previous step which is added custom there is a column named custom and the custom creates multiple tables that i want to combine so that is going to become a list if i just select that particular column and the list contains multiple tables that's what i want so i'm just going to write custom here and close the bracket press enter what i get is all the tables of this particular columns being combined together and shown up right here this is good enough now i don't really want to combine the tables because once i combine the table the data opens up and i get to see the entire data and i kind of lose the previous column which is the name of the sheet column so i really want to have just the name of the columns which are there captured in the three tables that i have here so i will wrap this formula in a new function called table dot column names and that's about it so i'm just going to say that hey why don't you combine all the data from the custom table and just get me the list of all the column names there are that means give me date give me sales rep give me customer don't really open up the data for all the columns i'm just going to commit to this formula and let's just see what we get so i get to see all the possible columns there are when the tables get combined and open up i just get to see the list let's just rename this particular list and let's just call this list as column names all right now i'm going to create another step and i create an effects step now instead of referring back to the previous step i will refer back to the added custom because in the added custom i have to expand these tables and alongside get the name as well so i will not really refer to the column name steps i will actually refer to the previous step so just add it custom press enter and i get the entire step right here now what i'm going to do is i will actually take this column and take this particular column right click and remove other columns because these are the two columns that i really want to have now i will expand that manually so i'll click on expand i will uncheck the original column name prefix and i will say okay now once i expand that manually all the names that are there in the tables get manually hard coded that's not a good thing we will automate that in just a while but the good thing is that alongside all these columns we now also have the name column that i really wanted to have alongside all the other columns now how do i solve this problem so i'm saying that table dot expand column and i'm expanding the table in the previous column so this is the table that i'm expanding this table is there in the custom column and i'm and i'm just writing the names of the columns manually so instead of writing the names manually i can just delete all the names which are there manually and i can fetch that from this step that i just created a while ago so i'm just going to write column names here close that bracket press enter it's just still going to give me the same result but now instead of hard coding the names the names are being fetched by the formula right here this is good to go i can just click on the home tab click on close and load and now we have the same query being loaded once again but let's just test out the results alongside we have the name of the sheet as well so i'm just going to go back to the excel in the excel now on sheet 1 i will add another column here so let's just call this as another column and i will throughout this column write my name for no good reason and i will save this uh excel file come back here and i will right click here and i will just do a refresh now i will have another column in there another column i will get to see my name and my name is only going to be across sheet1 and i have now also successfully got the name of the sheet which was one of the previous columns in the previous step so that was all about dynamically expanding columns especially when you're trying to combine the data from multiple data sources i give you a couple of approaches feel free to use any one of these approaches that suits your needs and to close this video a side note about my power query course if you're trying to learn power query right from scratch build up queries that can automate a bunch of your tasks in excel and in power bi why don't you take a look at my course is going to be extremely beneficial and of course just like always if you have any questions please feel free to put them down in the comments and i'll be glad to reply thanks so much for watching this guys and i'll catch you in the next one bye

Channel: Goodly

Views: 15,044

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

Id: oExuBdnHtrk

Length: 13min 6sec (786 seconds)

Published: Mon Nov 16 2020