Object reference not set to an instance of an object

[trevb]

So I have my PowerQuery working perfectly but when I apply and load the "Apply Query Changes" comes back with the error above.  I'm not sure what is causing this but I may have a suspicion.  My query generates a seperate record for each existing record between a start and end date.  I thus have a record for every day between these dates.

It all seems to work fine in the query, but the query does not load all the rows.  It occurred to me that just maybe the above error was happening because the number of rows was blowing out the memory.

It's a bit of a hotch potch 'cos I'm trying to debug it but it looks like this.

let
//Define things for use later on.
MinDate = Record.Field(Table.Min(Source{[Name="RoadmapCoreData"]}[Content],"StartDate"),"StartDate"), //Minimum start date in the source
MaxDate = Record.Field(Table.Max(Source{[Name="RoadmapCoreData"]}[Content],"DueDate"),"DueDate"), //maximum due date in the source
YearsBetweenMinAndMax=Date.Year(MaxDate) - Date.Year(MinDate), //This will give us the number of years between the minimum and the maximum years

//Load the source date.
Source = SharePoint.Tables("https://emckclac.sharepoint.com/sites/its3/SOLUTIONSTEAM/roadmap"),
RoadmapCoreData1 = Source{[Name="RoadmapCoreData"]}[Content],

NoOfDaysOfFunding= Table.AddColumn( RoadmapCoreData1,"NoDAYSFunding",each Duration.Days([DueDate]-[StartDate])),

//This will add a row for every month between the start of MinDate and the end of MaxDate to the table
PutDateTypesRight = Table.TransformColumnTypes(NoOfDaysOfFunding,{{"DueDate", type date}, {"StartDate", type date}}),
AddDays =
Table.ExpandListColumn(
Table.AddColumn(PutDateTypesRight,"Day", each List.Generate(()=>#date(Date.Year(MinDate),1,1), each _< #date(Date.Year(MaxDate)+1,1,1),each Date.AddDays(_,1))),"Day"),
AddFinancialYearStart = Table.AddColumn(AddDays, "FinancialYearStart", each if Date.Month([Day]) >= 8 then Date.Year([Day]) else Date.Year([Day])-1),

//Now work out what months will need some budget to do the work
FindActiveDays = Table.AddColumn(AddFinancialYearStart, "BudgetType", each if [Day] >= Date.StartOfMonth([StartDate]) then if Date.AddDays([Day],1)<=Date.EndOfMonth([DueDate]) then "Money" else "REMOVEME" else "REMOVEME"),
RemoveNonActiveDays = Table.SelectRows(FindActiveDays, each ([BudgetType] = "Money")),

CalculateBudget = Table.AddColumn(RemoveNonActiveDays, "Budget", each (([CostLow]+[CostHigh])/2) / [NoDAYSFunding]),

#"Added Custom" = Table.AddColumn(CalculateBudget , "Status", each if [StageValue]="Archived" then
if [Completed]=true then "Complete" else "No Longer Required" else
if [StageValue]="In-Flight" then "In Progress" else
if [StageValue]="Closing" then "In Progress" else
if [StageValue]="Forming" then "Preparing to Start" else "Waiting for Start"),
in
#"Added Custom"

I'm trying to use Months because there is no DateDiff function for working out the number of months between and when I create a column using a formula the query becomes very slow.  If someone has a way around that it might help.

Many thanks for your help

[andrewtegala]

Still having the same problem in the latest June 2017 release.

[Steve88]

Yeah, I found the same worked for me. Seems like an odd new feature.

[Drobinson1]

I just recieved the same error.  Trying to link two created columns.  Both are [PartCode]&[Warehouse].

[bradnywells]

Object reference not set to an instance of an object. exactly what it says, you are trying to use a null object as if it was a properly referenced object. Most of the time, when you try to assing value into object, and if the value is null, then this kind of exception occur. NullReferenceException indicates that you are trying to access member fields, or function types, on an object reference that points to null. That means the reference to an Object which is not initialized.

Wells