Forum Discussion
Solved
Inserting rows for missing values based on different lists
Hi PowerBI Community, I am pretty new to PowerBI and Power Query. I would like to add missing values to my Excel files, for the following type of table: test first name last name grad...
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.
Here is the Excel table I would like to obtain after the insertions:
| test | first name | last name | grade | field | classroom code | passed? |
| arithmetics 1 | Beth | Henson | 14 | Math | 10.4 | YES |
| arithmetics 1 | Chris | Connemara | 17 | Math | 10.4 | YES |
| arithmetics 1 | Edward | Shetland | 10 | Math | 10.4 | NO |
| arithmetics 1 | John | Appaloosa | 8 | Math | 10.4 | NO |
| arithmetics 1 | Jess | Halfinger | 18 | Math | 10.4 | YES |
| geometry 1 | Beth | Henson | 11 | Math | 10.4 | NO |
| geometry 1 | Chris | Connemara | 13 | Math | 10.4 | YES |
| geometry 1 | Edward | Shetland | 15 | Math | 10.4 | YES |
| geometry 1 | John | Appaloosa | 14 | Math | 10.4 | YES |
| geometry 1 | Jess | Halfinger | 15 | Math | 10.4 | YES |
| poetry 1 | Beth | Henson | 10 | English | 10.4 | NO |
| poetry 1 | Chris | Connemara | ABS | English | 10.4 | NA |
| poetry 1 | Edward | Shetland | 14 | English | 10.4 | YES |
| poetry 1 | John | Appaloosa | 13 | English | 10.4 | YES |
| poetry 1 | Jess | Halfinger | 9 | English | 10.4 | NO |
| arithmetics 1 | Peter | Malinois | 14 | Math | 10.2 | YES |
| arithmetics 1 | Patricia | Groenendal | 12 | Math | 10.2 | YES |
| arithmetics 1 | Gloria | Australian | 18 | Math | 10.2 | YES |
| arithmetics 1 | Melvin | Setter | 15 | Math | 10.2 | YES |
| arithmetics 1 | Jess | Spaniel | 11 | Math | 10.2 | NO |
| geometry 1 | Peter | Malinois | 10 | Math | 10.2 | NO |
| geometry 1 | Patricia | Groenendal | 16 | Math | 10.2 | YES |
| geometry 1 | Gloria | Australian | ABS | Math | 10.2 | NA |
| geometry 1 | Melvin | Setter | 13 | Math | 10.2 | YES |
| geometry 1 | Jess | Spaniel | 16 | Math | 10.2 | YES |
| poetry 1 | Peter | Malinois | 13 | English | 10.2 | YES |
| poetry 1 | Patricia | Groenendal | 9 | English | 10.2 | NO |
| poetry 1 | Gloria | Australian | 12 | English | 10.2 | YES |
| poetry 1 | Melvin | Setter | 8 | English | 10.2 | NO |
| poetry 1 | Jess | Spaniel | 9 | English | 10.2 | NO |
Here's one way to do it with a self merge in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. The field and classroom fields are null in the added rows. If those are constant values for those tests, this code could be adapted to use those values.
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 Columns" = Table.SelectColumns(Source,{"first name", "last name"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "test", each List.Distinct(Source[test])),
#"Expanded test" = Table.ExpandListColumn(#"Added Custom", "test"),
#"Merged Queries" = Table.NestedJoin(#"Expanded test", {"first name", "last name", "test"}, Source, {"first name", "last name", "test"}, "Expanded test", JoinKind.LeftOuter),
#"Expanded Expanded test" = Table.ExpandTableColumn(#"Merged Queries", "Expanded test", {"grade", "field", "classroom code", "passed?"}, {"grade", "field", "classroom code", "passed?"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded test",null,"NA",Replacer.ReplaceValue,{"grade"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"ABS",Replacer.ReplaceValue,{"passed?"})
in
#"Replaced Value1"
Pat
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.