Power Query

For automating any type of document reading, we can use POWER QUERY editor which is not a different Application but accessible through Microsoft excel only. So the entire thing is done using POWER QUERY only.

Use case video - https://www.youtube.com/watch?v=NRn4FVrO128

Process to follow Use Case:

1. By Choosing “GET DATA” option in excel we can access POWER QUERY and Then amongst all other options we have to choose “From Folder” option since we want to merge many files, so we choose the “folder option”.
2. After Power Query screen comes up, we just click on Transform data. Since we want to set up the automations, and then we click on Merge files
3. Now next as we know everything in POWER QUERY is a table, hence POWER QUERY by itself transforms our PDF (in this case a GST return) to various tables. So, we choose table 10 (ITC table)
4. That’s all our Basic Live linking is done, we choose “Close and Load”. Now if we want more specific like  suppose “Other than reverse Charge” from ITC Table, than we have to remove the top row and than in 2^nd stage make top row as header,  and than remove alternate rows, (first row to delete no 6, no of rows to delete 7, no of rows to keep : 5)
5. Close and Load and now when we change the files in the desired location than our live excel data shall change accordingly.

Importance of Use Case

Automation can play a very crucial role in our office management. Sending client details matching data as per 3b and GSTR 1 and all such things can be clumsy at times. If we can extract relevant information from the returns on an automated basis and capture them in desired formats, our work of matching them shall become easy.

Whether it is 2 returns or 20 returns, it is all done in a click of a button. Hence use of AI in automation cannot be ignored. If Manually we are asked to extract data from returns there are not only high chances of error but also it can become time taking with volumes, With this technique the time saved in doing the task specially with large volume is unimaginable.

Alternate Tools

Alternatively, websites like Pabbly Connect can do a lot of automations but they are very costly compared to Power Query which is Free.