Dears,
would appreciate, if you can help with Power Query. i would like to add 3 more columns into my table Last Submission Date, Last Validation Date and Last Validation Status. how to find Last Submission Date i did like this: choose all Submission Date columns => Add Column = > Date => Latest. after this i need somehow to lookup values from Validation Date and Validation Status... if there are any way to do it by formulas, it will be also great (i have tried MAX, MATCH, VLOOKUP: for some cased results were not correct 🙁 ). attached file as an example. many thanks in advance for any idea.
Hi Alex,
Thanks for sharing your file, however I couldn't follow what you mean by: "after this i need somehow to lookup values from Validation Date and Validation Status..."
Can you please give me some examples of the desired result?
Thanks,
Mynda
Dear Mynda
Firstly, let me thanks for your great work your are doing and sharing your experiences!
Originally, i have a simple documentation tracker which contain sequence of Submission Date; Validation Date and Validation Status (Approved; Rejected; Under Validation). 1st Submission Date ... 4th Submission Date (or even N-Submission Date). for one ID i can have 4 Submission of the documents, for other - only 2 Submission. if Status is Under Validation than Validation Date cell will be emty
Result i want to get: i want to generate only 3 Unique Columns: Submission Date; Validation Date and Validation Status (in my file marked by green) which can show me the latest status of each document.
i have updated file and added some formulas. Firstly, i have found the last Submission Date by MAX function, then i VLOOKUP this last Submission Date to find our to Last Validation Date and Status. how to make it via Power Query unfortunately no idea... (maybe with Record.Field ...)
Hi Alex,
Thanks for clarifying. In the attached file I took a copy of your original data and removed columns B:D so I could create these using Power Query. You'll see it's on a sheet called 'Source Data' in a table called 'Source_Table'.
I loaded this table to Power Query and you'll see the query is called 'Status' and the output is on the sheet also called 'Status'. Please inspect the query to see the steps I took.
Please let me know if you have any questions.
Mynda
Dear Mynda
many thanks! that was exactly i need! and the code was really not difficult...
Great 🙂 glad I could help.
