Data Loading Tricks in Power Query

hi you're with chandeep goodly once again and in this video i'm going to talk about a few interesting data loading tricks in power query let's start all right trick number one is that how do you apply a dynamic get the latest file from a folder kind of a filter in power query take a look at this folder we have a couple of files in that folder five six seven eight nine and ten and maybe i wanna have the latest file that i just created in that particular folder i don't really wanna have the old data just the latest data now if you did not know the trick that i'm gonna talk about what you would end up doing is that in this particular folder you will delete all the files and just keep one file so that power query is able to read that latest file but that is a bit of manual work let's just see how do we automate that all right i'm in power query and the first step that i have done in power query is to just connect my folder to my query and you can see that i have a function folder.files and that's where i have the name of the path of the folder and i can take a look at all the files which are there in that particular folder it's pretty simple you can actually do that now once you connect the folder to power query what you get is a couple of things of properties of that particular file so i can take a look at the name of the file the extension of the file and alongside i get three other date-based properties which is date accessed modified and created i can actually go ahead and open up the filter for date created and over there i can apply a filter and i can say hey keep the latest files over here i can say hey keep the latest file and this is a dynamic latest filter you can take a look at the formula bar latest is a dynamic filter and it is going to take a look at that folder take a look at all the files and the date created of that files and keep the latest file over there now do not get confused by 2010 your 2005 file could have been the most recent created file in that particular folder and in that case it's going to actually keep 2005 file not 2010 file so just be aware of that apart from this latest filter that we have just applied you can also apply a couple of other interesting filters if you wish so if i just maybe open up this filter in the date and time filters i can actually go ahead and take a look at previous three months files two months files one year file it's a pretty standard filter just with the user interface you can actually go ahead and apply these filters to make it more dynamic all right trick number two is that how do you create a dynamic path for all the files in the folders that you're connecting inside of power query now i have also done a video on how do you create a dynamic path in excel i'm going to leave a link to that video in the blog post but for now let's just take a look that if we had to do that in power query how can we actually do that for that you'll have to actually create a parameter so i'm just going to open up the queries pane right here in the home tab i have something called as parameters i'm actually going to create a parameter which is a new parameter let's just give this parameter a name and let's just call this as a path parameter you can actually call it anything and you can write some description if you would like the type of the data is going to be a text because the path of the folder is going to be a text and the current value let's just write abc for now i mean abc is not a legit path but for now let's just write abc i'm just going to say okay and a path gets created which is where the current value is this now let's just go over to the data loading tricks and let's just go copy that particular path uh which is the path of the folder which is where all my files are kept just carefully copy that part do not copy the inverted commas just the path press ctrl c and i'm just going to come back to that particular path and start to edit that parameter and i'll feed that particular path right here so i'm just going to feed that path right here and that's the current value that i will input in that particular folder i'm just going to click on ok and that's the path that is that now if you would like to change the path of the folder obviously you're just going to come here and change the path right here now as of now we just have one single query in our particular data model which is just data loading tricks that i'm just playing around with now you could have many data sets that are just coming from different sources and if they're all combined into one particular folder you can all connect them to the single path so at every single query you'll have to actually go to the source step and maybe uh just over here delete that entire thing and maybe just link that to the path right here and close the bracket and press enter now this path is nothing but the parameter that i just declared which is right here and it's going to start to read that data from that particular path and the query still works absolutely fine that's one thing that you'll have to do for all the queries and once you actually do that all the queries are going to be referring to that dynamic path and in case your path changes there is only one single place that you'll have to make a change and this will all be dynamic all right trick number three has to do with data load performance now if you have a lot of excel files which are trying to connect it to power query those data sets can be very slow as compared to data from a csv file so if you have the option of converting your excel files to csv files you're going to see a tremendous boost in terms of data load when it comes to getting the data into power query now chris webb has a blog post where he's talked about that thing where he tests the csv as compared to excel and csvs are way faster as compared to excel now if converting all your excel files to csv files seems a bit of work in the comments section of the same blog post eric has mentioned about a power automate connector and you should actually take a look that it can help you convert all your excel files into csv files all right trick number four is creating helper tables in your power bi model a lot of times when you're trying to create a power bi model you would need the assistance of a helper table that you're going to use it for some or the other purpose now one way is that you create that table in excel and connect that excel file to power query now in that scenario you'll have to take care of that excel file that becomes a little of a liability what i will suggest is that you use dax to create such tables or you can actually copy and paste the values from excel and directly connect that to power query you will have flexibilities to edit those tables as well now the dax way i'm actually going to link a video which is about the data table function and you can take a look at that and you learn how to actually create tables using the data table function the other way is that you can actually copy and paste the data from your excel and paste the data inside of power query and you can actually work with that table let me just show you that all right i'm in excel and here is where you can see that i have a very simple two columnar table that i'm just going to use it in my model so i can actually just go ahead and press ctrl c to copy this particular data set note that i'm just copying this particular data i'm not really connecting this excel file to my power query i'm just going to press ctrl c i'm going to come back to power query in the home tab i do have an option called enter data this option is also available in power query as well as power bi so i'm just going to click on enter data and that's where i will have the ability to just paste that ctrl v just to paste that right here i can give this but table a particular name let's just say table and i can click on ok and that table actually gets created like a copy paste of that particular table now if i have to maybe change the values of the particular table or maybe add a few values what i can actually do is i can go back to the source step in the source step i can just maybe click on the gear icon and that's where i have the entire table and that's where if i want i can add more columns add more values although it's a little bit of work to be able to come here and edit the values but at least you don't have to take care of that extra excel file that got created which is where you have to take care of that excel file all right trick number five is that how can you create a date right from the name of the excel file a lot of times you would not really have a dedicated date column in the data set and you may have to pick up the date column from the name of the excel file perhaps take a look in the name of the excel file we have the name of the file and dot xlsx and maybe i'd like to call all of these files or have a date column in all of these excel files is first january of that particular year so first chan 2005 first channel 2006 so on and so forth and all the data i'd like to label that against 1st of jan of that particular year how do i actually create the date from the name of the file it's very very simple all that i'm going to do is i'm actually going to come to the add column and write a quick formula whatever i'm doing with the formula you can actually also do that using the interface as well so i'm actually going to come here in the add columns tab in the custom column i will start to write one simple formula the first thing that i'm actually going to do is i'm just going to get rid of the dot xlsx that is being added right here so i'm just going to come here and let's just call this as a date first of all so date and i'm just going to call this caller function called text dot replace and i'm going to say that hey here is my text this particular name column is my text so i'm just going to click on that name column and i'm going to say that from this name column i want you to get rid of dot xlsx so the old text to replace is dot xlsx and the new replacement is nothing so just close that bracket i'm just going to click on ok and i do get a column which is where i have all the years as of now what i'm now going to do is i'm just going to wrap around this particular thing into a date function so i'm just going to write equals to hash date now hash date function actually accepts three things it accepts the year the month and the date in that particular order note that the text dot replace actually gives me a particular name of the file which is nothing but the ear of the file but this is a text and i want the year as a number so i'm just going to convert this text into a number i'm just going to say number.from and i'm going to say hey this actually delivers you a text which is although a number why don't you convert that into a number so number dot from this actually gives me the year in which format in the numeric format i'm just going to maybe press comma and i'm now going to write the month the month is going to be 1 which is january and the date is again going to be 1. i close that bracket i commit to the formula and now what i have is a date created you can assign a data type of a date and once you actually open up the data set of this particular column you will have the data set and alongside you will have the year column or the date column that we have actually created now there could be a lot of ways in which you can actually create the ear all that i was trying to do was give you an example as you can actually create a date from the name of the excel file itself all right those were a few interesting data loading tricks let me know if you have any questions around these and i'll be glad to help in the end a quick shout about my dax and my power query course in case you're starting out with power bi and you need structured help with learning power query or with dax learn the fundamentals first and then proceed on to solving more difficult problems i will highly recommend that you take a look at my course it's going to be highly beneficial thanks so much for watching this and i'll catch you guys in the next one [Music] cheers [Music] you

Channel: Goodly

Views: 45,320

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

Id: 4S_aore7ezg

Length: 10min 25sec (625 seconds)

Published: Fri Apr 09 2021