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...
I can code in 9 different programming language, Does that help? Does this increase my chances of getting hike? How did I learn? How do I switch on/off mode for different languages? Programming languages are about compatibility of the system, about the legacy of the utility and comfort of programmer. Be it either of these in picture, its important to know what you are doing and have logic in mind. Like any other spoken language, computer languages are about communication in the easiest possible way. My personal favorite is Python. Its free, its mature, its flexible, its fast, its easy to learn and most importantly its a popular language in demand. Simple: Python is a readable and understandable language. Free: This programming technology is free and open. Compatible: Python offers compatibility with various platforms. Object-oriented: Python supports procedure-oriented and object-oriented programming. Libraries: There are ...
This is to call for applications to " AI Residency Program " recently launched by Shell India. This a 2-year, full-time, immersive programme, which allows data scientists, AI engineers and computational scientists to gain experience working on a variety of AI projects across all Shell businesses. Recently, Shell India has launched its own, specialized and global programme – bringing digitization – in India to newer heights. Join us, and make history through influencing the future of energy. Along with this, we are also conducting a hackathon for sustainable and affordable energy which gives the winners a direct entry to interviews for Shell AI Residency Program. PS: Jobs will only be offered to people with relevant experience as mentioned in the page while hackathon is open to everyone. Participate and be a part of Journey to a cleaner and more sustainable tomorrow.
Comments
Post a Comment