Hi Richard,

You can add a custom column that checks if the text in Trips = "Minutes" and if so, it brings in the value from the Minutes column otherwise it enters null. You can then copy that value up and filter out the rows you don't need.

Mynda

I know nothing about Custom Columns and the way it checks as you have asked.  I'm guessing it may have something to do with the M language that I haven't gotten to in the course yet.

I have completed sections 1 through 4 and have to do the rest.  The M language is covered in section 6.

I'm guessing I need to complete that section first.

Am I mcorrect?

Hi Richard,

Adding custom columns is covered in session 4.13, however I recommend you use the simpler Conditional Column technique covered in session 6.14. Fill is covered in session 4.10. Let me know how you get on.

Mynda

I see how the Conditional column works however it doesn't solve my issue.

The 33.5 minutes goes over and the fill does the trick however every day the Minutes have a different value, different than 33.5.

I don.t need the 33.5 moved over each day I need the whatever the new value is moved over is.

Thanks

Richard

Hi Richard,

Please create a mock up Excel file containing your data (without the link to the PDF) in it's before format that covers the scenarios you describe, and also show your final desired result you'd like to achieve. I can then help you further.

Mynda

Will do.  However the report comes over in .pdf format.

I should have sent you an Excel mock up to begin with.  I'm new to the Excel Forum routine.

If I could make it work through the .pdf data supplied as the report is very complex and we do not have direct connect to the data base because of security. 

We could get a different report format but it would take the report people time to create it.

I will get on creating the Excel Mack up but it would take me some time but no where near the time the report people would need to create the report.

I had thought that using PQ I could create a second duplicate table and use the Left Outer join connection to pull over the value I want.

Your thoughts?

Hi Richard,

You can share the PDF if there's nothing confidential in it if you like, but it shouldn't be difficult to copy and paste the table from the PDF into an Excel spreadsheet and load it to Power Query from there. That will just make it easier for both of us because you can also manually modify the data to show me a mock up of how you're wanting the output to appear.

Mynda

I tried to copy the .pdf but it doesn't copy, must be a security setting.

I could access it with PQ and took it all the way where you see it now.

I saved it as a Excel file as you requested.

I need the 33.5 value moved up to the where the 6 is.

Every day the value 33.5 is subject to change.

I know how to convert with PQ as text vs. numbers.

I will load the .pdf files into a folder, (about 9 files a day).

I will then Transform using PQ.

The data is confidential I suppose due to be not even being able to copy it.

I Only hope this doesn't get too slow as the data builds.  so far we have 178 files in the folder and starting now going forward will be going from 3 files a day to 9 files a day.

Other people are entering the data by hand and they are making lost of mistakes.

If I can get this to work it will at least reduce the data entry errors.

Please help.

Thanks,

Richard

Hi Richard,

Thanks for sharing the file. In the attached you'll see the steps I took to return your desired result into a table in Rows 11 and 12. I hope that helps. Please let me know if you have any questions.

Mynda

This is all new to ne.

I see your results but don't know how to view your stepa.

Is the method of viewing your steps in the PQ course.

If yes where.

If no how do I get to see your steps.

Thanks,

Richard

I figured out how to see your steps'

I'll go over them and see how they work out.

Thanks much.

Richard.

Mynda,

I tried the Query you sent me and it works perfectly and furthermore after testing it I am understanding more how this all works.

I have one last question that needs answering.

Question: **********************************************************************

     We've been entering all this data by hand for the last 3 months and are trying to automate the process by using PQ.

     The data we've entered is in the desired format however the report data needs to be massaged with PQ.

     The automated process will be us placing the reports into a folder and doing a PQ refresh.

     WE need to combine the newly PQ created data into the table we created by hand.

         Two (2) different sets of data, the Automated PQ data and the hand entered data, put together so it appears like the hand entered data.  I assuming with PQ this can procedure can be automated?

     Don't need to know how at this point I just need if it can be done at all?

End Question:**************************************************************************

I think we can now close out this topic.

I intend to finish the course ASAP so I can move on with this project.

You have been most helpful, the course is excellent and I looking forward to completing the Dashboard course as my next learning assignment.

THANKS,

Richard

Hi Richard,

Great to hear you're making progress and enjoying the course.

In answer to your second question, this part I'm not certain about "...so it appears like the hand entered data", because I don't know what the hand entered data looks like, and I suspect it's not in a tabular format. In which case PQ can't do that, but a PivotTable probably could.

If you can start a new question and provide examples of the two datasets and the final desired output I can answer your question.

Mynda