Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
trevb
Resolver I
Resolver I

Object reference not set to an instance of an object

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

 

 

24 REPLIES 24
JulianNoarmenn
New Member

what data source are you using? 


Depending on the data source used, the fix is ​​different. Take a look at the list of fixes according to the datasource here: https://powerbipoint.com/2024/01/02/power-bi-fix-the-error-object-reference-not-set-to-an-instance-o...

Anonymous
Not applicable

Hi everyone, I have found a solution to this error while solving another one.

 

If you use this setting it goes through the error:

 

laawqb_0-1628513821178.png

 

trutz
Advocate III
Advocate III

I had a similar issue with my data model where everything was working fine in Power Query, but upon "Apply changes" I received the error. I started to disable the load for all tables and left only my main fact table. The error message changed and showed an error with a data type mapping. Apparently something in the DB backend had changed and the data type could no longer be correctly applied. In the end it would help if Power BI Desktop could provide better error handling on this.

arielu
New Member

Yesterday I found a solution for this error message, but maybe it will work only in some cases.

 

I use Windows 10, and yesterday I did the Power BI upgrade to the July release (Versión: 2.48.4792.721 64-bit (julio de 2017)). After restarting, Power BI didn't open, kept running the starting window during 5 minutes, and finally opened a window with the error message "Object reference not set to an instance of an object". After closing the error message, it kept running the starting window, but never open.

 

In our company there's a proxy server and security rules that modify the configuration of proxy exceptions. Recently, TI department modified the rules and added the exception "*powerbi.com*;" in the field "Do not use proxy server for addresses beginning with:". I deleted the text *powerbi.com* from the exception field, and the problem was solved.

 

I don't know if this can solve all the instances of the error, but in my case it works.

MartinCarlsson
New Member

... also have this problem with the Feb 2017 version.

 

Where do I find and older version of PowerBI?

tomasp
Regular Visitor

So I found a solution - the only thing needed was to connect the fields in model in opposite diretion! I had no idea, that it matters, which field you click first when creating a connection, but it apparently does.

tomasp
Regular Visitor

Same problem here...but old version probably won't help since I already saved the document in Feb version 😞 

charlesutton
New Member

I too have confirmed that this seems like a bug for the Feb release. I rolled back to Dec version and I'm no longer experiencing the issue.

Getting the same bug in the Feb release with mysql

How do I roll back to December please?

 

 

Two step process.

1. Using the Feb 2017 Power BI installer package file (.msi) file, I re-ran the file and chose the uninstall option.

2. And then, I re-installed Power BI with the Jan 21, 2017 .msi file; version 2.42.4611.701 or 2.42.4611.901

 

Fortunately, for me, I retained all of my downloaded .msi files.

 

Hope that helps.

@dsandberg

 

Thanks - that really helps.

 

Can you raise a support ticket?

 

I cannot raise a support ticket.  The portal bugs out. 

DataGuy2016
Advocate II
Advocate II

This appears as a bug in the desktop release Version: 2.43.4647.541 64-bit (February 2017)

IS there a bug open on this topic?

The Support page is also down.

 

Its a bad day for the PBI team in Microsoft.

 

This problem is causing me a lot of pain and I am considering moving to Tableau

I was able to restore to the Jan 2017 version and correct the issues I was having.

Having the same issue; cannot model anything (get the error when connecting fields to establish relationships).

 

I skipped the January version due to all the issues folks were reporting, now would like to revert back to December - does anyone have available to share?

 

 

Anonymous
Not applicable

Hi, It took me a couple of hours to figure why Power BI wasn't creating the relationships out.

Error message was:

"Unexpected error Object Reference not set to an instance of an object". (Very informative)

Checked the data was clean, trying different data sources that were working before, etc. Then I tried creating the relationship in the other direction and it worked without any issues XD.

With that I mean when I drag and drop from Table A to Table B, the exception occurs; when I drag and drop from Table B to Table A, works.

 

Hope this helps! 

 

Thanks - worked for me... you saved me hours 😄

This problem still persists for me. Unfortunately it can not be solved by a change of relationship direction, since it only occurs when grouping rows in a table coming from the Google Analytics connector.

 

I have this problem since the release of the April update. I'm currently still running the March version, since also the May update did not fix this obvious bug. Any help/fix is highly appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.