Remove Duplicates and Sort Chronologically by row excluding specific columns

[memote1]

I have a table with columns for demographic information, vaccine date information and titer date/result information. By row, I need to remove duplicates and sort vaccine dates chronologically. I don't anticipate needing more than 4 columns for Vaccine Dates, but there is always the potential for more.

Raw Data

People Soft ID

Name

Birth Date

Date of MMR vaccine 1

Date of MMR vaccine 2

Date of MMR vaccine 3

Date of MMR vaccine 4

Date of Measles vaccine 1

Date of Measles vaccine 2

RS.Date of MMR vaccine 1

RS.Date of MMR vaccine 2

RS.Date of MMR vaccine 3

RS.Date of MMR vaccine 4

Date of Measles IgG 1

Result of Measles IgG 1

Date of Measles IgG 2

Result of Measles IgG 2

RS.Date of Measles IgG 1

RS.Result of Measles IgG 1

1

Betty Boop

2/8/1999

2/8/1999

5/9/1999

1/1/2023

Reactive

2

Darth Vader

6/10/2015

8/10/2012

5/10/2015

3

Chicken Little

4

Donkey Kong

9/23/2006

10/23/2006

10/23/2006

5/1/2022

8/1/2022

Desired Output

People Soft ID

Name

Birth Date

Date of MMR vaccine 1

Date of MMR vaccine 2

Date of MMR vaccine 3

Date of MMR vaccine 4

Date of Measles IgG 1

Result of Measles IgG 1

Date of Measles IgG 2

Result of Measles IgG 2

RS.Date of Measles IgG 1

RS.Result of Measles IgG 1

1

Betty Boop

2/8/1999

5/9/1999

1/1/2023

Reactive

2

Darth Vader

8/10/2012

5/10/2015

6/10/2015

3

Chicken Little

4

Donkey Kong

9/23/2006

10/23/2006

5/1/2022

8/1/2022

My M Code

#"Reordered Columns" = Table.ReorderColumns(#"Expanded RS",{"People Soft ID", "Name", "Birth Date", "Date of MMR vaccine 1", "Date of MMR vaccine 2", "Date of MMR vaccine 3", "Date of MMR vaccine 4", "Date of Measles vaccine 1", "Date of Measles vaccine 2", "RS.Date of MMR vaccine 1", "RS.Date of MMR vaccine 2", "RS.Date of MMR vaccine 3", "RS.Date of MMR vaccine 4", "Date of Measles IgG 1", "Result of Measles IgG 1", "Date of Measles IgG 2", "Result of Measles IgG 2", "RS.Date of Measles IgG 1", "RS.Result of Measles IgG 1"}),
#"Extracted Date" = Table.TransformColumns(#"Reordered Columns",{}),
TableCombine = Table.CombineColumnsToRecord(#"Extracted Date","Vacc Dates", {"Date of MMR vaccine 1", "Date of MMR vaccine 2", "Date of MMR vaccine 3", "Date of MMR vaccine 4", "Date of Measles vaccine 1", "Date of Measles vaccine 2", "RS.Date of MMR vaccine 1", "RS.Date of MMR vaccine 2", "RS.Date of MMR vaccine 3", "RS.Date of MMR vaccine 4"}),
VaccList = Table.FromList(List.Sort(List.Distinct(List.RemoveNulls(List.Combine(Record.ToList([Vacc Dates]))))),Record.FieldValues, type table)
in
VaccList

I am getting this error message: 

Any help you can give me would be much appreciated!

Thank you!

[v-kpoloju-msft]

Hi @memote1,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @lbendlin, for your inputs on this issue.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.  I have implemented the solution using DAX measures rather than Power Query (M code), as DAX offers a more transparent and manageable approach, especially for maintaining and enhancing the model over time.

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[memote1 · Accepted solution]

@v-kpoloju-msft 

Thank you for your solution,

Unfortunately, I don't have access to DAX. However, I was able to come up with a very clunky and time consuming solution!

1. Reference the original table

2. Remove all the columns except "People Soft ID" and all the Vaccine date columns

3. Use the solution from this post from @dufoq3 

4. Remove empty columns found in this post

5. Merge this new table with the original. 

This is my resulting code:

[lbendlin]

By row, I need to remove duplicates and sort vaccine dates chronologically. 

that is impossible unless they all take the same vaccinations in the same order.