Forum Discussion
Remove Duplicates and Sort Chronologically by row excluding specific columns
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!
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:
3 Replies
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.
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.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:
