Microsoft.Mashup.Evaluator.Interface.ErrorException: Nullable object must have a value

[gavinfb]

Hello,

I have a report that uses an ODBC connector to get the data.  It used to refresh automatically without any issues however, since a few monthly it is failing the refresh with the error below.  Any idea why, please?  It is even not allowing me to refresh via the Desktop as well when I get this error.

thanks 

Gavin

----------------------------------------------------

Feedback Type:
Frown (Error)

Error Message:
Nullable object must have a value.

Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Nullable object must have a value. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Nullable object must have a value. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Nullable object must have a value. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Nullable object must have a value. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Nullable object must have a value. ---> System.InvalidOperationException: Nullable object must have a value. ---> System.InvalidOperationException: Nullable object must have a value.

[v-yuta-msft]

@gavinfb ,

What data source are you connecting to? Could you share the power query code for further analytics?(Do mask the sensitive data)

Regards,

Jimmy Tao

[gavinfb]

Hello Jimmy,

The data is in AS400 and I am using ODBC connector to make the bridge between Power BI and AS400.

Here is the Power Query Code:

let
    Source = Odbc.Query("dsn=AS400 - Production", "WITH#(lf) EVAL (stn1, stn2, snb1, snb2, ord1, ord2, iden, brcd, ship, stk1,#(lf) qtal, qtca, qtrp, qtpp, qtpk, qtll, qtsh, ntun, curl) as#(lf) (SELECT ststn1, ststn2, spsnb1, spsnb2, sdord1, sdord2, sdiden, sdbrcd, smshic, sdstk1, /* AL+CA RP PP */#(lf)   sdqtal, sdqtca, sdqtrp, sdqtpp, 0, 0, 0,#(lf)   case when smpref='1' and smchac=' ' then sdntun else 0 end, smcurl#(lf)  FROM DBFLIB.SOODP inner join#(lf)   DBFLIB.SOOMP on sdord1=smord1 and sdord2=smord2 inner join#(lf)   DBFLIB.PASOP on sdord1=soord1 and sdord2=soord2 and soact='Y' inner join#(lf)   DBFLIB.PASPP on sosnb1=spsnb1 and sosnb2=spsnb2 inner join#(lf)   DBFLIB.PASTP on spstn1=ststn1 and spstn2=ststn2#(lf)  WHERE sdstk1=01 and sdiden in('Q','E') and sdcatg<>'4' and sdact='Y' and#(lf)   ((sdqtal<>0 and sdqtca<>0) or sdqtrp<>0 or sdqtpp<>0)#(lf)  UNION ALL#(lf)  SELECT ststn1, ststn2, spsnb1, spsnb2, sdord1, sdord2, sdiden, sdbrcd, bmship, bdstk1, /* PK */#(lf)   0, 0, 0, 0, bdqty, 0, 0,#(lf)   case when smpref='1' and smchac=' ' then sdntun else 0 end, smcurl#(lf)  FROM DBFLIB.PABDP inner join#(lf)   DBFLIB.PABMP on bdbox1=bmbox1 and bdbox2=bmbox2 inner join#(lf)   DBFLIB.SOODP on bdord1=sdord1 and bdord2=sdord2 and bdseq=sdseq inner join#(lf)   DBFLIB.SOOMP on bdord1=smord1 and bdord2=smord2 inner join#(lf)   DBFLIB.PASPP on bmsnb1=spsnb1 and bmsnb2=spsnb2 inner join#(lf)   DBFLIB.PASTP on spstn1=ststn1 and spstn2=ststn2#(lf)  WHERE bdstk1=01 and bdbox1>=2018 and bmldn2=0 and (bdact='Y' or stact='Y' or spact='Y' or spshdt=curdate())#(lf)  UNION ALL#(lf)  SELECT ststn1, ststn2, spsnb1, spsnb2, sdord1, sdord2, sdiden, sdbrcd, bmship, bdstk1, /* LL SH+BL */#(lf)   0, 0, 0, 0, 0,#(lf)   case when ldshdt='0001-01-01' then bdqty else 0 end,#(lf)   case when ldshdt<>'0001-01-01' then bdqty else 0 end,#(lf)   case when smpref='1' and smchac=' ' then sdntun else 0 end, smcurl#(lf)  FROM DBFLIB.PABDP inner join#(lf)   DBFLIB.PABMP on bdbox1=bmbox1 and bdbox2=bmbox2 inner join#(lf)   DBFLIB.SOODP on bdord1=sdord1 and bdord2=sdord2 and bdseq=sdseq inner join#(lf)   DBFLIB.SOOMP on bdord1=smord1 and bdord2=smord2 inner join#(lf)   DBFLIB.SOLDP on bmldn1=ldldn1 and bmldn2=ldldn2 inner join#(lf)   DBFLIB.PASPP on ldsnb1=spsnb1 and ldsnb2=spsnb2 inner join#(lf)   DBFLIB.PASTP on spstn1=ststn1 and spstn2=ststn2#(lf)  WHERE bdstk1=01 and bdbox1>=2018 and (bdact='Y' or stact='Y' or spact='Y' or spshdt=curdate())#(lf)  UNION ALL#(lf)  SELECT ststn1, ststn2, spsnb1, spsnb2, sdord1, sdord2, sdiden, sdbrcd, pdship, pdstk1, /* PK */#(lf)   0, 0, 0, 0, 1, 0, 0,#(lf)   case when smpref='1' and smchac=' ' then sdntun else 0 end, smcurl#(lf)  FROM DBFLIB.ESPDP inner join#(lf)   DBFLIB.SOODP on pdord1=sdord1 and pdord2=sdord2 and pdseq=sdseq inner join#(lf)   DBFLIB.SOOMP on pdord1=smord1 and pdord2=smord2 inner join#(lf)   DBFLIB.PASPP on pdsnb1=spsnb1 and pdsnb2=spsnb2 inner join#(lf)   DBFLIB.PASTP on spstn1=ststn1 and spstn2=ststn2#(lf)  WHERE pdstk1=01 and pdbox1>=2018 and pdldn2=0 and (pdact='Y' or stact='Y' or spact='Y' or spshdt=curdate()) and pdbccd=' ' and pdpkcd='1'#(lf)  UNION ALL#(lf)  SELECT ststn1, ststn2, spsnb1, spsnb2, sdord1, sdord2, sdiden, sdbrcd, pdship, pdstk1, /* LL SH+BL */#(lf)   0, 0, 0, 0, 0,#(lf)   case when ldshdt='0001-01-01' then 1 else 0 end,#(lf)   case when ldshdt<>'0001-01-01' then 1 else 0 end,#(lf)   case when smpref='1' and smchac=' ' then sdntun else 0 end, smcurl#(lf)  FROM DBFLIB.ESPDP inner join#(lf)   DBFLIB.SOODP on pdord1=sdord1 and pdord2=sdord2 and pdseq=sdseq inner join#(lf)   DBFLIB.SOOMP on pdord1=smord1 and pdord2=smord2 inner join#(lf)   DBFLIB.SOLDP on pdldn1=ldldn1 and pdldn2=ldldn2 inner join#(lf)   DBFLIB.PASPP on ldsnb1=spsnb1 and ldsnb2=spsnb2 inner join#(lf)   DBFLIB.PASTP on spstn1=ststn1 and spstn2=ststn2#(lf)  WHERE pdstk1=01 and pdbox1>=2018 and (pdact='Y' or stact='Y' or spact='Y' or spshdt=curdate()) and pdbccd=' ' and pdpkcd='1')#(lf)#(lf)SELECT#(lf) case when strsdt>'0001-01-01' then strsdt end ""Date"",#(lf) right(digits(stn1),2) || '/' || digits(stn2) ""Truck"", fwname ""Forwarder"", right(digits(snb1),2) || '/' || digits(snb2) ""S/N"",#(lf) ship ""Ship"", sidesc ""Shipment Code"", brcd ""Br"", iden ""Iden"", curl ""Curr"",#(lf) sum(min(qtal, qtca)*ntun) ""Qty Al+CA"", sum(qtrp*ntun) ""Qty RP"", sum(qtpp*ntun) ""Qty PP"", sum(qtpk*ntun) ""Qty Pk"",#(lf) sum(qtll*ntun) ""Qty LL"", sum(qtsh*ntun) ""Qty Sh+Bl"",#(lf) case when stact='N' then 'Inact' when stdedt>'0001-01-01' then 'Exit' when stdcdt>'0001-01-01' then 'Close'#(lf)      when stdgdt>'0001-01-01' then 'On Gate' when stdddt>'0001-01-01' then 'On Dock' when stdddt='0001-01-01' then 'New' end ""Status"",#(lf) stact ""Truck Act"", spact ""S/N Act""#(lf)FROM#(lf) EVAL left outer join#(lf) DBFLIB.PASTP on stn1=ststn1 and stn2=ststn2 left outer join#(lf) DBFLIB.PASPP on snb1=spsnb1 and snb2=spsnb2 left outer join#(lf) DBFLIB.CMSHP on stk1=sistk1 and ship=siship left outer join#(lf) DBFLIB.CMFWP on ststk1=fwstk1 and stfrwr=fwforw#(lf)GROUP BY#(lf) stn1, stn2, fwname, snb1, snb2, brcd, curl, iden, ship, sidesc, strsdt,#(lf) stact, stdedt, stdcdt, stdgdt, stdddt, stact, spact#(lf)ORDER BY#(lf) strsdt, stn1, stn2, fwname, snb1, snb2, ship, brcd, curl, iden"),
    #"Filtered Rows1" = Table.SelectRows(Source, each ([Date] <> null)),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Qty Sh+Bl", "Qty Sh"}, {"Date", "Truck Date"}, {"Truck", "Truck ID"}, {"Status", "Truck Status"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns1", {"Curr"}, #"FX rates", {"Currency name"}, "FX rates", JoinKind.LeftOuter),
    #"Filtered Rows" = Table.SelectRows(#"Merged Queries", each ([Truck Date] <> null)),
    #"Expanded FX rates" = Table.ExpandTableColumn(#"Filtered Rows", "FX rates", {"Rates"}, {"Rates"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FX rates", "Qty AL+CA.1", each [#"Qty Al+CA"]*[Rates]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Qty AL+CA.1", "$ AL+CA"}}),
    #"$ RP" = Table.AddColumn(#"Renamed Columns", "$ RP", each [Qty RP]*[Rates]),
    #"$ PP" = Table.AddColumn(#"$ RP", "$ PP", each [Qty PP]*[Rates]),
    #"$ PK" = Table.AddColumn(#"$ PP", "$ PK", each [Qty Pk]*[Rates]),
    #"$ LL" = Table.AddColumn(#"$ PK", "$ LL", each [Qty LL]*[Rates]),
    #"$ SH" = Table.AddColumn(#"$ LL", "$ SH", each [Qty Sh]*[Rates]),
    #"Changed Type" = Table.TransformColumnTypes(#"$ SH",{{"$ SH", type number}, {"$ LL", type number}, {"$ PK", type number}, {"$ PP", type number}, {"$ RP", type number}, {"$ AL+CA", type number}, {"Qty Sh", Int64.Type}, {"Qty LL", Int64.Type}, {"Qty Pk", Int64.Type}, {"Qty PP", Int64.Type}, {"Qty RP", Int64.Type}, {"Qty Al+CA", Int64.Type}, {"Ship", type text}}),
    #"$ In Process" = Table.AddColumn(#"Changed Type", "$ In Process", each [#"$ PP"]+[#"$ PK"]+[#"$ LL"]),
    #"# In Process" = Table.AddColumn(#"$ In Process", "# In Process", each [Qty Pk]+[Qty PP]+[Qty LL]),
    #"$ Total Potential" = Table.AddColumn(#"# In Process", "$ Total Potential", each [#"$ AL+CA"]+[#"$ RP"]+[#"$ PP"]+[#"$ PK"]+[#"$ LL"]),
    #"# Total Potential" = Table.AddColumn(#"$ Total Potential", "# Total Potential", each [#"Qty Al+CA"]+[Qty RP]+[Qty PP]+[Qty Pk]+[Qty LL]),
    #"Added Conditional Column" = Table.AddColumn(#"# Total Potential", "Distri or Dealer", each if [Br] = "DI" then "Distri" else if [Br] = "CI" then "Distri" else "Dealer"),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Conditional Column",{{"$ SH", "$ SH + Bill"}, {"Qty Sh", "Qty Sh + Bill"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"$ Total Potential", Int64.Type}, {"# Total Potential", Int64.Type}, {"# In Process", Int64.Type}, {"$ In Process", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "$ Diff", each [#"$ Total Potential"]-[#"$ In Process"]),
    #"$ Diff" = Table.TransformColumnTypes(#"Added Custom1",{{"$ Diff", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"$ Diff", "# Diff", each [#"# Total Potential"]-[#"# In Process"]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"# Diff", Int64.Type}, {"$ In Process", type number}, {"$ Total Potential", type number}})
in
    #"Changed Type2"

thank you

Gavin 

[Aaarvark]

Same exact error started happening to me around the same time on an ODBC connection to an Oracle db from Excel.  I opened the query to edit, hit refresh and reload.  Started working again.  But I would like to know what caused it, too.

[aluxh]

I have the same problem. Then, I downgrade the Power BI to May's version, and it is working now.

[parishdapbi]

I'm getting same problem, again connecting to AS400 aka iSeries aka IBM DB i with the ODBC driver.