How can I give a unique id to the patient based on the patient's name and DOB?

[t_raj]

I am stuck at one point. I am creating a sales report for my company. for that each month I get a txt file. The size of each file is around 150mb. For that, I also have to combine old data. data for the years 2019,2020 and 2021. now the thing is, I also get patient data. now, I want to give the patient's id. Based on the patient's name and DOB. The challenge is Patient's id should only be given if that patient coming for the first time. if the patient comes again after two years, an old id should be given. One more condition; all patient information should not be included in a data model. I have to delete all columns except the patient's id. How can I do this? please help me. I am stuck at this point for while. Thank you in advance. Keep going.

Sample data:

name	DOB
Rock	12/12/1998
Rock	12/12/1998
Johnson	17/02/1967
Pepe	24/07/1997
Pepe	24/07/1997
smith	11/01/1956
smith	11/01/1956
smith	03/06/1969
Zarna	19/03/2003

 Data :

End Result:

Suppose this date is for the month of April 2021 and is given to the patient. suppose now "Rock" again comes in October 2021, the same id should be given to ROCK. In this case "1".

[t_raj · Accepted solution]

Solution:

Add index to subgroups in Power Query - YouTube 

in my case, I have to do it a little bit differently than what is in the YT video. In this video, she gave an index to the table but in my case, I have to give an index to each group and then expand the table. 

[Vijay_A_Verma]

Need to look at some sample data to work out the steps for you. 

How to Get your Question Answered Quickly - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523