Inserting rows for missing values based on different lists

Hi SimoniAr80 

 

Try the M code below. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZPRS8MwEMb/FenzkLWdOh+nlA1hOuiTjD0c69kGsqQkUfG/NykK7e5S8tIkhV++7767HI8ZGOG6Czpxtjd5tsie0HV+2aGyWvlNvvKfPQw/8+VtOL1XdXZaUPS5M8KGVSuFFzAQkIdkvGq+wTR+U3foJKhmQAj++sbSL7oLdjd9D1JrG7TXySza4HsH8kOoFk0AKPzvu0XtWfPDx5XHRCcYG1WZJsnmdJfGMinNNHiKMiHFVXsdjyj0tFKtFJamNOLYOlccy4hyhZapLK30ccby9TQd0A3MHqRQemjzVcbFzCs4gDPiLILhrdGoUDUgA1YkX7GV2gwXbD6tM94FKDrRcxfsUX6JwNToHNfpOfgvvboHJVDSJ1FEngQX2zKNjEV2HzU94ZlyyzSS1hqXHA0YVyoZzhgbKZZM6CitER6ZjSJRnUa1TtMlSUX9nn4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [test = _t, #"first name" = _t, #"last name" = _t, grade = _t, field = _t, #"classroom code" = _t, #"passed?" = _t]),
    #"Removed Other Columns1" = Table.SelectColumns(Source,{"first name", "last name", "classroom code"}),
    #"Removed Duplicates1" = Table.Distinct(#"Removed Other Columns1"),
    classroom_tests = Table.Distinct(Table.SelectColumns(Source,{"classroom code", "test"})),
    #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates1", {"classroom code"}, classroom_tests, {"classroom code"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"test"}, {"test"}),
    Custom1 = Table.NestedJoin(#"Expanded Table", {"first name", "last name", "classroom code", "test"}, Source, {"first name", "last name", "classroom code", "test"}, "DataTable", JoinKind.LeftOuter),
    #"Expanded DataTable" = Table.ExpandTableColumn(Custom1, "DataTable", {"grade", "field", "passed?"}, {"grade", "field", "passed?"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded DataTable",{"test", "first name", "last name", "grade", "field", "classroom code", "passed?"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"classroom code", Order.Ascending}, {"test", Order.Ascending}, {"field", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"field"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"ABS",Replacer.ReplaceValue,{"grade"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"NA",Replacer.ReplaceValue,{"passed?"})
in
    #"Replaced Value1"

 

Note that when you come to below step, sort the table by columns "classroom code", "test", "field" one by one (don't change this sort order). Then you will see the null rows display below the same test in the same classroom. Then use fill down feature to fill in them. 

 

Final result

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.