Similar to what some others have already suggested. This is still going off your original table and handling the "Given" filtering you want.
This is somewhat manually doing an unpivot action, with a separate treamtent on your two "date of measles" columns vs. the "mmr status" and "date of mmor" pairs. All steps below from Source to SortDates are performing this faux unpivot + filtering. Another way to do this (perhaps with better performance) would be to merge your status/date columns and then unpivot everything except ID and DOB, do your {birthday<, is "Given", not null} filtering, and then do a Group+Accumulate to filter our dates per the 24 day rule.
Source (note: added another row #13 to more directly test 24 day rule)
SortDates
The next steps, RemoveNullDates to ExpandValidDateRecords, then filter out dates before first birthday, group and filter our the dates that don't adhere to the 24 day rule, and then dynamically figure out the number of columns to expand.
Final table:
M code for adanced editor:
let
Source = #"Table 1",
//Manually set the column names for the ID and DOB columns
IdCols = {"People Soft ID", "DOB"},
//Column names that start with "Date of Measles"
MeaslesCols = List.Select(Table.ColumnNames(Source), each Text.StartsWith(Text.Lower(_), "date of measles")),
//Column names that start with "Date of MMR"
MmrDateCols = List.Select(Table.ColumnNames(Source), each Text.StartsWith(Text.Lower(_), "date of mmr")),
//Column names that start with "MMR Status"
MmrStatCols = List.Select(Table.ColumnNames(Source), each Text.StartsWith(Text.Lower(_), "mmr status")),
//Pairing the MMR Status and Date columns names together together.
//We assume that the MMR Status and Date columns are in the same order.
MmrPairedCols = List.Zip({MmrStatCols, MmrDateCols}),
//For each column name in MeaslesCols, create a table with the ID columns and dates
//from the current Measles column put into consolidation column, "Good Date"
MeaslesTables = List.Transform(
MeaslesCols,
//For each column name in MeaslesCols...
(thisMeasleCol) =>
let
//Select the ID columns and the current Measles column
idAndMeasleCol = Table.SelectColumns(Source, IdCols & {thisMeasleCol}),
//Rename the current Measles column to "Good Date"
renameMeasleCol = Table.RenameColumns(idAndMeasleCol, {thisMeasleCol, "Good Date"})
in
renameMeasleCol
),
//For each column name pair in MmrPairedCols, create a table with the ID columns and dates
//from the current MmrDate column name where corresponding status is "Given".
//Again, putting all dates into consolidation column, "Good Date"
GivenMmrTables = List.Transform(
MmrPairedCols,
//For each Status/Date pair of MMR column names...
(MmrPair) =>
let
//Per previous Zip step, status is first element of the pair (list)
mmrStatus = MmrPair{0},
//Per previous Zip step, date is second element of the pair (list)
mmrDate = MmrPair{1},
//Select the ID columns and the current MMR column pair
idAndMmrPair = Table.SelectColumns(Source, IdCols & MmrPair),
//Filter the table to only include rows where the MMR status is "Given"
filterToGiven = Table.SelectRows(idAndMmrPair, each Record.Field(_, mmrStatus) = "Given"),
//Remove the status column as no longer needed
removeStatCol = Table.RemoveColumns(filterToGiven, {mmrStatus}),
//Rename the current MMR date column to "Good Date"
renameMmrDateCol = Table.RenameColumns(removeStatCol, {mmrDate, "Good Date"})
in
renameMmrDateCol
),
//Combine all Id columns + "Good Date"s from previous steps
CombineAllDates = Table.Combine(MeaslesTables & GivenMmrTables),
// Sort the table by ID and date.
// This is necessary for our use of GroupKind.Local in the next step
SortDates = Table.Sort(CombineAllDates, {{"People Soft ID", Order.Ascending}, {"Good Date", Order.Ascending}}),
//Remove any null dates from the "Good Date" column
RemoveNullDates = Table.SelectRows(SortDates, each ([Good Date] <> null)),
//Filter out any dates that are before the first birthday.
FilterBeforeFirstBD = Table.SelectRows(RemoveNullDates, each [DOB] = null or [Good Date] >= Date.AddYears( [DOB], 1 ) ),
GroupValidDates = Table.Group(
FilterBeforeFirstBD,
{"People Soft ID", "DOB"},
{{
"ValidDates",
each List.Accumulate(
//iterate over the dates in the current group
[Good Date],
//Seed is empty list
{},
//Defining accumulator function. See here for more info: https://powerquery.how/list-accumulate/
(state, current) =>
//If the current Good Date is greater than or equal to last accepted date + 24 days
//OR there are no dates in the list yet (state is empty)
if List.IsEmpty(state) or current >= Date.AddDays( List.Last(state), 24 )
//If yes: add the current Good Date to the list of accepted dates
then state & {current}
//IF no: we don't do anything and move on to next Good Date to check
else state
),
type { date }
}},
//The order of dates that we iterate through with List.Accumulate needs to be in ascending order.
//We can ensure this by using GroupKind.Local. The default, GroupKind.Global, does not guarantee this.
GroupKind.Local
),
//Calc the maximum number of valid dates in any group.
MaxValidDates = List.Max(List.Transform(GroupValidDates[ValidDates], List.Count)),
//Create a list of all possible field names we may need
ValidDateFieldNames = List.Generate(() => 0, each _ < MaxValidDates, each _ + 1, each "MMR " & Text.From(_ + 1)),
//Create a record type with the field names we generated above.
ExtractRecord = Type.ForRecord(
Record.FromList(List.Repeat({[Type = type date, Optional = false]}, MaxValidDates), ValidDateFieldNames),
false
),
//Transform the ValidDates list of dates into a record with the record type we generated above.
TransformDatesToRecord = Table.TransformColumns(
GroupValidDates,
{
"ValidDates",
each Record.FromList(
//If we have less dates than max, add nulls to the end of the list to fill it out
_ & List.Repeat({null}, MaxValidDates - List.Count(_)),
//Our record type provides Record.FromList with the field names and types
ExtractRecord
),
//Using record type again so the column type is set correctly
//(ensures properly typed columns after expansion)
ExtractRecord
}
),
//Expand the records into separate columns by referring to the field names we generated above
ExpandValidDateRecords = Table.ExpandRecordColumn(TransformDatesToRecord, "ValidDates", ValidDateFieldNames)
in
ExpandValidDateRecords
I believe you are looking for custom sort in a particular group i.e., remove nulls and sort the dates within a group.
If this is true, try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dVJbDsQgCLzKxu9NELQKZ2l6/2usgLa02X6YwDjMjI99T5i+CQmoAwrLaD5hYQbisdFkssSb47snenB1FcBB6KaJq1ZymcimEE1lFesl2t0YgE0zkQnUgbApCj5DutHFYA+4DaACdqCcs1u+NKrS3FraqMdBzYlNR5HN70HENXUwBq/ngFHDcH+9JdZDCgQu/+FqzhJDz0b5YlpU13asG1BbKWPtujqN2W/c07bZnFbkQ8jPJijoS3SfsQcGsrpfMN/LMEv2XITr21U92ghSDC/LTaA+jI8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"People Soft ID" = _t, #"Date of MMR vaccine 1" = _t, #"Date of MMR vaccine 2" = _t, #"Date of MMR vaccine 3" = _t, #"Date of MMR vaccine 4" = _t, #"Date of Measles vaccine 1" = _t, #"Date of Measles vaccine 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"People Soft ID", Int64.Type}, {"Date of MMR vaccine 1", type date}, {"Date of MMR vaccine 2", type date}, {"Date of MMR vaccine 3", type date}, {"Date of MMR vaccine 4", type date}, {"Date of Measles vaccine 1", type date}, {"Date of Measles vaccine 2", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"People Soft ID"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"People Soft ID", Order.Ascending}, {"Value", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"People Soft ID"}, {{"aalr", each _, type table [People Soft ID=nullable number, Value=date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([aalr], "Index", 1, 1, Int64.Type)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"People Soft ID", "Value", "Index"}, {"Custom.People Soft ID", "Custom.Value", "Custom.Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.People Soft ID", Int64.Type}, {"Custom.Value", type date}, {"Custom.Index", type text}}),
#"Added Prefix" = Table.TransformColumns(#"Changed Type1", {{"Custom.Index", each "MMR " & Text.From(_, "en-US"), type text}}),
#"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Custom.Index]), "Custom.Index", "Custom.Value"),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Custom.People Soft ID", "People Soft ID"}})
in
#"Renamed Columns"
Note: If this is what you want, then you can find the optimizing ways based on your data. Like doing the pivot inside the group itself or some other ways. Let us get the basic output you need first!
Output:
To implement a custom sort within a group, such as by PeopleSoft ID, and organize the dates to display them in grid format, I used the following method in Power Query:
-
Import the data.
-
Unpivot the columns.
-
Sort the data.
-
Group the data by the desired field "PeopleSoft ID"
-
Create an index within each group.
-
Expand the grouped data and add a prefix text "MMR".
-
Pivot the data back into its original format.
-
Rename the columns as needed.
Finally, adjust the process to suit your specific requirements.
Hope this helps!
If you dont want duplicates, add the step in between Sorted Rows and Grouped Rows
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"People Soft ID", Order.Ascending}, {"Value", Order.Ascending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"People Soft ID"}, {{"aalr", each _, type table [People Soft ID=nullable number, Value=date]}}),
Then the outputput will be
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
