Forum Discussion
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.
Hi Pat,
Thanks for your help and taking some time for my question.
Your response partly answered:
To fill in the null values in the added rows, I would like to use a conditionnal formula but the issue I have is that there are several classrooms with different students associated in the same file: I don't see how a simple conditional formula could replace those null values by the correct "classroom code" and "field".
Do you know a way to fill:
- the "field" column with "Math" when the "test" column contains arithmetic or geometry, or English when the "test" column contains "poetry"?
- the "classroom code" with 10.2 if last name is "Malinois" or "Groenendal" or "Australian" or "Setter" or "Spaniel"; 10.4 for another list of last names; etc.
Thanks,
Best Regards.
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.