try the DLookUP
If that don't work let us know more than happy to help.

Below is what is listed in the NCMR Main Table-Access_Import_PowerQuery. How would I change this to display all this but make the NCMR Number the trigger field to add it to the table in Excel?

let
    Source = #"NCMR Main Table-Access_Import_Headers",
    #"Appended Query" = Table.Combine({Source, #"NCMR Main Table-Access_Import_Type1", #"NCMR Main Table-Access_Import_Type2", #"NCMR Main Table-Access_Import_Type3", #"NCMR Main Table-Access_Import_Type4", #"NCMR Main Table-Access_Import_Type5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"NCMR Number", type text}, {"Date", type date}, {"NCMR Type", type text}, {"Part Number", type text}, {"Lot Quantity", Int64.Type}, {"Number Rejected", Int64.Type}, {"Discrepancy Type", type text}, {"Discrepancy", type text}, {"Discrepancy Qty", type text}, {"Disposition", type text}, {"Cause", type text}, {"Machine Number", type text}, {"Oper1", type text}, {"Oper2", type text}, {"Oper3", type text}, {"Oper4", type text}, {"FORMAL CAR REQUIRED?", type logical}, {"Q-220 Analysis Req?", type logical}, {"Closed?", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Discrepancy Type] <> null and [Discrepancy Type] <> "")
in
    #"Filtered Rows"

don't know much on power query but the last line sticks out with filtered rows... try changing the [Discrepancy Type to [MCMR Number]  and see what happens.  after that I have to pass and see if another will help.