E Invoice Format in Excel Download for Banking Made Easy

E invoice format in excel download for Banking

hello friends welcome to an interesting video where i am going to import bank statement in pdf format into excel using power query so over to my screen for demonstration i have taken hdlc bank statement so naturally very first thing people try to do is that select this and right click now unfortunately you cannot see any menu called copy because this bank statement is protected to be copied so you can just see edit text now how to grab this in excel let's see i'm using office 365 version of excel which supports all these options not all excel might support importing from pdf so let's begin so i will go to data tab then get data from file and i have from pdf so in your excel this option might not be there because you may might have older version of excel which doesn't support import of pdf so i'll click here and let us browse the file okay okay now you can see on the screen uh power query preview has opened so it is scanning this pdf file let's wait for this okay so now let's try to analyze what data it got okay multiple table it tried to extract so let's say i think last table must contain our data yeah it's continuing now moment i will press transform data a full-fledged power query editor window will open so that we can sanitize this because remember it would not be even directly in imported format okay we may have to clean up some rows some columns or some invalid data okay so as you can see there is lot of garbage data so we need to get rid of this garbage first okay so now how to remove garbage rows now let's see in this bank statement i can see narration being extended next line okay so in the next line there is just utr number which i am actually not really interested so maybe we can get rid of this line so if you see in the next slide there is null okay null length is the blank line so let's instruct power query to remove all the rows which are having null or empty rows so i would go here okay and i will say remove empty so remove empty rows okay now you can see the garbage rows have been removed okay now still we have some garbage data here so if you carefully see column number there which this is this is not required totally so i will click this right click and remove it and one more garbage is remaining so if you see withdrawal and deposit amounts i can see null that means empty there so i want zero instead of empty so what i can do i can select both of these columns go to transform and perform find replace action so let me click here find replace value so find null and replace it with 0. okay let's perform this okay and now the last step we need to instruct our query that what type of data is there in all the individual columns so as you can see it has detected date perfectly narration and perfectly okay this value date it is thinking it is text but actually it's a date so i can click on this box on the top end of the field and let's treat it as date and this three columns i would select and we can say that its data type is decimal number okay so i would go to transform data type and these are having decimal number okay so i will click and make it decimal number okay so we are done with this let me rename this sheet let's say hdfc bag okay and again i will go to home and close and load too so here you can specify in which worksheet of excel you want to import so i would say in new worksheet and i would keep this as table so let's click ok and import it okay so friends can you see this data perfectly imported friends let me make you aware of one thing so it's not necessary that all bank statement might be importable okay power query might not be able to grab data from every bank statement so it purely depends on how the structure of that bank statement is and whether power query is able to import it to a nearest table structure so i hope you enjoyed this video please try this and if you like it please subscribe to the excel kida youtube channel you