SOLVED

Summarize from multiple table(s)

%3CLINGO-SUB%20id%3D%22lingo-sub-3220005%22%20slang%3D%22en-US%22%3ESummarize%20from%20multiple%20table(s)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3220005%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%3C%2FP%3E%3CP%3EI'm%20trying%20to%20extract%20data%20in%20a%20summary%20file%20from%2012-sheet%20tables%2C%20where%20each%20sheet%20is%20a%20month.%3C%2FP%3E%3CP%3EThe%20tables%20I've%20taken%20into%20consideration%20are%20from%20a%20government%20GST%20portal.%3C%2FP%3E%3CP%3EThe%20current%20practice%20is%20to%20create%20manual%20links%20by%20copying%20from%20the%20required%20PDF%20file.%20However%2C%20I%20converted%20the%20PDF%20file%20into%20an%20Excel%20file%20for%20further%20processing.%3C%2FP%3E%3CP%3ESample%20file%20attached.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AhfNqwYEWYSHg2uQ4jtTyYkrOE6J%3Fe%3DMuNO37%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AhfNqwYEWYSHg2uQ4jtTyYkrOE6J%3Fe%3DMuNO37%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20in%20separate%20sheet%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EMS%20Office%20Version%202016%3C%2FP%3E%3CP%3EP.S.%20I%20would%20highly%20appreciate%20if%20someone%20could%20suggest%20me%20whether%20I%20can%20accomplish%20this%20task%20via%20Power%20Query%20Editor%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3220005%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3221303%22%20slang%3D%22en-US%22%3ERe%3A%20Summarize%20from%20multiple%20table(s)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3221303%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20sir%20Riny_van_Eekelen.%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20very%20much%20appreciate%20for%20giving%20me%20your%20time.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EHowever%2C%20I'm%20afraid%20that%20I%20have%20reached%20a%20dead%20end%20to%20this%20thread.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EMaybe%20there's%20not%20a%20lot%20to%20do%20about%20it.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EIt%20was%20my%20fault%3B%20I%20had%20converted%20the%20PDF%20to%20Excel%20and%20that%2C%20sir%2C%20was%20a%20cause%20for%20all%20those%20concerns.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMaybe%20next%20time%20I'll%20try%20and%20figure%20it%20out.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUntil%20then...%3C%2FP%3E%3CP%3E%3CSPAN%3EBid%20adieu%2C%20monsieur.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there!

I'm trying to extract data in a summary file from 12-sheet tables, where each sheet is a month.

The tables I've taken into consideration are from a government GST portal.

The current practice is to create manual links by copying from the required PDF file. However, I converted the PDF file into an Excel file for further processing.

Sample file attached.

https://1drv.ms/x/s!AhfNqwYEWYSHg2uQ4jtTyYkrOE6J?e=MuNO37 

Data in separate sheet


MS Office Version 2016

P.S. I would highly appreciate if someone could suggest me whether I can accomplish this task via Power Query Editor

 

2 Replies
best response confirmed by a4avinash (New Contributor)
Solution

@a4avinash Oh my goodness! That's not very easy, but if you make sure that the data dump for every month has exactly the same lay-out, you could extract all rows that have amounts in them and create a "somewhat organized" table that should enable you to get the summary you need. See attached file.

 

Note: I needed to change the lay-out for May as it was not the same is for April. The first two rows where not similar, inserted  row 9 and added a par 6.2 at the bottom. Just check and see what I did.

 

 

Thank you, sir Riny_van_Eekelen.

I very much appreciate for giving me your time.
However, I'm afraid that I have reached a dead end to this thread.
Maybe there's not a lot to do about it.
It was my fault; I had converted the PDF to Excel and that, sir, was a cause for all those concerns.

Maybe next time I'll try and figure it out.

 

Until then...

Bid adieu, monsieur.