Hello,
I am importing data for a spreadsheet from a pdf. Sometimes the data i need is in Table002, and sometimes it is in Table003. Therefore, I want to use Page001 for the source instead of the tables. However, it varies what row the data I am needing is on. How do I tell it to look for a "word" and then delete all the rows above that word so that I can then start my calculations and other transformation?
Any ideas??
Thanks
RH
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I have attached a picture. I need all rows above the word "Language Arts" deleted. On some files it is on row 19, some its 21, etc. I need it to look for the words "Language Arts" and then delete all rows above it.
Screenshot 2024-01-29 101710.png
Thank you,
RH
A file we could use would be super helpful. But the short answer is to use PowerQuery for it.
I can't send a file, because the data is for a school student and contains personal data. I have attached a picture. Sometimes "Language Arts" is in row 19 and sometimes in row 20. I need power query to look for the word and then delete the rows above it.
Screenshot 2024-01-29 101710.png
RH
Without a file it would be extremely difficult if not impossible to help.
The student's name could be anonymized to Student1.
Other personal data could be deleted or similarly anonymized as in change address to 123 Street 1 City 1, TX 12345 etc.
It may be that someone would only need a small sample, perhaps one set of data with Language arts in row 19 and another with Language arts in row 20.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Ok, I am adding a file I have changed the personal data on. I am really needing help with this!
Emily Transcript.pdf
Thank you in advance!
Here is the Mcode to Dynamically delete the top rows before "LANGUAGE ARTS" Remember PQ is case specific
Please Login or Register to view this content.
ALAN,
YOU ARE AWESOME!!!! THANK YOU SO MUCH!!!
Rena Hearn
Rena,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Now I have realized that sometimes it adds an extra column in and it messes up my formulas. Any suggestions as to how to handle this? I need someway to make it consistent so that it doesn't error when a column is not found.
Need a clear example of your issue. Your explanation is too vague to respond.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks