Inserting rows for missing values based on different lists

[SimoniAr80]

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	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	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	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

The "passed?" column uses the formula =IF(D2>=12, "YES", "NO"). I then expanded the formula down for all rows.

For the poetry 1 test, Chris Connemara from classroom 10.4 is missing. Also, Gloria Australian's results are missing for the geometry 1 test.

I would like to create a function to add a row for each of this missing values, with the corresponding test, first name and last name, field, classroom code. Moreover, the "grade" would be ABS (for absent) for these students and the "passed?" column would display "NA".

Thanks in advance for your help,

Best Regards,

SimoniAr80

[SimoniAr80]

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

[mahoneypat]

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

[SimoniAr80]

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.

[v-jingzhang · Accepted solution]

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.