So for this one, Here is how my workflow works:
1. First I use Text-to-column tool to get all the names, gender and the number of time it appear.
2. Then I use the Data cleansing tool to get only the year from the file names. I remove all letters, whitespace, special character in the configuration.
3 Then I use the select tool to rename all the columns and change datatype for number of times and year.
4. Then I use the filter tool to separate the male and female into two different data-stream.
5. Then I use summarize tool on both data stream to get the total sum of number of times each name appear group by year and name.
6. Then I sort by year, by number of times and by name.
7. Then I summeraze again and select the first name and number for each year.
8. Then I join both the data stream and get the final results.
9. Then I use the select tool to rename and select the required column.
10. Then I use the browse tool to display final result.
Thanks for the Challenge.