Yeah, very mediocre title to the blog but imagine if google SEO would ever find it if I used my imagination here and named it "My Jump Into The Herd Of Data Scientists". This was the first choice but you know yeah! Beside writing the aim of this blog is also to find you and ensure you like following this one. Therefore, subscribe to stay updated
So the next thing is to clarify that this blog is about a journey, a never-ending marathon, and inspirations that led me here today. No algorithms!
Search This Blog
Power Query - I : Dynamic Connection To Latest Data in Database
If coding/automation is not about us fooling the machine to get our work done, we haven't yet exploited our powers enough. I, in my daily life, do all sorts of problem-solving involves complex coding, cloud computing, etc. but I get my real kick when I solve the smallest problem like hacking excel, or automation of workflow using power query. Here is my latest gig, I tried to connect to an excel in SharePoint and ensure that every time the database is updated my query picks the latest file. This is the first time I am using power query and it sounds so powerful. So without further delay, let's see what I did:
1. Connect Excel to DataBase: Open an excel file, Select Data from the green ribbon, go to Get Data, select the drop-down on From File, and connect to share point select From SharePoint Folder.
Fig1: Select DataBase
Once you select the database which is SharePoint in our case, you will get a window like this popping up to connect to it. Put in site url (not the link to file) only connect is to the folder and select OK.
Fig2: Enter URL
2. Find out the file you want to connect: After clicking OK, excel connects you to the SharePoint folder and gives you a preview of all the files inside the folder. Since we are aiming to select only one file of our choice we need some modification.
Fig3: Folder Preview
To access the full list select Transform Data to open the power query editor where you can select the file of your choice.
Fig4: Power Query Editor
Now, all you need is to take some steps to identify the latest file.
3. Ensure you have the latest file selected: To get this, pick the Date Modified column, and sort it in Descending column to get the latest file on the top.
Fig5: Sort the Files
4. Select the first file: So yes, almost there! once your files are sorted, you need to ensure that only the first one is selected - Home -- Keep Rows -- Keep Top Rows -- 1, once you have only one file in your list, now you need to ensure that the name doesn't get hardcoded, so to get this done you need to select the Content column and click on double arrow next to the column name to combine.
Fig6: Combine Latest File
And you are done! Just need to check if the file name is not there in the query.
5. Check the query to confirm the file name is not hardcoded: To look at the query select the relevant table in the file and click Advanced Editor to find out your file name is not there.
Fig7: Query Check
And guess what, you can straight up use this query than performing all these steps. Hope this is helpful Stay tuned!!.
Yes, times are difficult but that's what opportunity seekers make the best out of. This pandemic has resulted in a lot of unexpected changes. Most of us don't have a plan anymore because it seems hard to believe when and how our lives would resume. What will the new normal be? Is it ever getting back to the same? Will I ever be able to live my good old life again? How will demand for skills change in near future? What shall we do to maximize the gain from this slow-paced life? With so many questions in their mind, I had an opportunity to talk to a wonderful group of audience in a webinar organized by EAGE RGPIT SC . When I asked them "What is in your mind?" This was their response. People are worried about their careers. With fancy data lingos, everyone is seeking to learn more about them and trying to prepare for a secured tomorrow. Data science has become more and more popular over the last decade. Being a data scientist is now a software engineer of yesterday. Eve...
Knowledge is important publications are not. Your degree might help you get somewhere but to grow you need to really know beyond your grades. This is becoming super popular with so many successful colleges drop out folks around yet it's important to know it all, why did they drop out?because they had an Idea! Very important to talk about, while everyone was studying securing good grades, what were you doing? Alright, you said you didn't want to part of a herd then what? Did you find your kingdom on your own? Did you make sure you utilized the time well? Did you do all that you wanted? If yes you might already know by now where your interests lie, if not yet you sure need to give more thoughts to it. Back to grades, I would say a straight no what matters is your attitude. Are you a hard-working successful fellow or a smart-working professional. Hard-work can take you anywhere but it can also take others there as well. Then, if you are competing, if you want to stand-...
Comments
Post a Comment