filter problem

[Anonymous]

I have an issue with Power Query in Power BI desktop where I'm losing records.  When troubleshooting I discovered that if I apply a step to filter missing student X by their ID, the record is displayed, such as:

Term = 202002

Student ID = 99999999

Effective Term = 201801

Include = Yes

However, if I apply a filter to include all students where Include = Yes without first filtering for just that student, that student is lost while others are included.  If it makes a difference (can't see why it should) the Include field is conditional based on Effective Term <= Term.  For this particular report I'm retaining 18,044 records while losing 4 records that should also be retained.  

I'm hoping that somebody can help because this is driving me absolutely bonkers!

[Greg_Deckler]

Can you post your code from Advanced Editor?

[Anonymous]

The code is below.  If I search for the problematic ID (column named Pidm) before the step highlighted in red and bold, it shows up with an Include value of "Yes".  After the highlighted step which filters to include only records with "Yes", it is gone.  If I filter for just that ID before the highlighted step - so that it is the only row left - it is maintained through the rest of the steps.  I have identified two separate IDs that are being dropped through this process.

let
Source = #"SATURN SFRSTCR",
#"Removed Other Columns" = Table.SelectColumns(Source,{"TermCode", "Pidm"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Duplicates", "TermPidm", each Text.Combine({[TermCode], Text.From([Pidm], "en-US")}, ""), type text),
#"Merged Queries" = Table.NestedJoin(#"Inserted Merged Column", {"Pidm"}, #"SATURN SGBSTDN", {"Pidm"}, "SATURN SGBSTDN", JoinKind.LeftOuter),
#"Expanded SATURN SGBSTDN" = Table.ExpandTableColumn(#"Merged Queries", "SATURN SGBSTDN", {"EffTerm"}, {"EffTerm"}),
#"Added Conditional Include" = Table.AddColumn(#"Expanded SATURN SGBSTDN", "Include", each if [EffTerm] <= [TermCode] then "Yes" else "No"),
#"Filtered Rows Include" = Table.SelectRows(#"Added Conditional Include", each ([Include] = "Yes")),
#"Grouped Rows" = Table.Group(#"Filtered Rows Include", {"TermCode", "Pidm", "TermPidm"}, {{"MaxEffTerm", each List.Max([EffTerm]), type text}}),
#"Merged Queries1" = Table.NestedJoin(#"Grouped Rows", {"Pidm", "MaxEffTerm"}, #"SATURN SGBSTDN", {"Pidm", "EffTerm"}, "SATURN SGBSTDN", JoinKind.LeftOuter),
#"Expanded SATURN SGBSTDN1" = Table.ExpandTableColumn(#"Merged Queries1", "SATURN SGBSTDN", {"Styp", "Resd"}, {"Styp", "Resd"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded SATURN SGBSTDN1",{"MaxEffTerm"})
in
#"Removed Columns"

[Anonymous · Accepted solution]

I've stumbled upon something that seems to have resolved the problem.  The report was pulling information from a table which had an effective term for student records.  You have to pull the maximum effective term prior to or equal to the term being reported so that student data for that term is correct.  I filtered that table and discovered three values that could not be correct - "000000", "210003", and "999999".  I filtered out all of those values.  Even though none of the students I was losing had records with those bad entries, the act of removing them allowed all data for all students to be retrieved.

I wish I understood the "why" behind all of this, but until then I'll apply what I've learned to all the reports I build using that table.