How to Troubleshoot Power BI Data Refresh Errors: Step-by-Step Guide

Power BI data refresh is a critical process that ensures your reports and dashboards reflect the most current data. However, this process can sometimes fail, leading to outdated or incorrect information being displayed. Understanding how to troubleshoot these errors is essential for maintaining the integrity of your data analysis. This guide will walk you through the common causes of Power BI data refresh errors and how to resolve them.

 

How to Troubleshoot Power BI Data Refresh Errors

Step 1: Identify the Error Message

 

The first step in troubleshooting is to identify the specific error message you are receiving. Power BI provides detailed error messages that can help pinpoint the issue. You can find these messages in the refresh history or the notification pane.

 

Example Error Message:

“`
“Data source error: The key didn’t match any rows in the table.”
“`

 

Step 2: Check Data Source Credentials

 

One common cause of refresh errors is incorrect data source credentials. Ensure that the credentials used to connect to the data source are up-to-date and have the necessary permissions.

 

Example:

– Go to the dataset settings in the Power BI service.
– Check the data source credentials and update them if necessary.

 

Step 3: Verify Data Source Accessibility

 

Ensure that the data source is accessible at the time of the refresh. If the data source is offline or unreachable, the refresh will fail.

 

Example:

– Attempt to connect to the data source using the same credentials outside of Power BI to verify accessibility.

 

Step 4: Review Query Changes

 

Changes to the underlying data or query structure can cause refresh errors. Review any recent changes to the data model or queries to ensure they are not causing the issue.

 

Example:

– Revert to a previous version of the query and attempt a refresh to see if the error persists.

 

Step 5: Inspect Gateway Configuration

 

If you are using an on-premises data gateway, ensure that it is configured correctly and is running the latest version.

 

Example:

– Check the gateway status in the Power BI service.
– Update the gateway to the latest version if necessary.

 

Step 6: Analyze Dataset Size and Limits

 

Power BI has limitations on dataset sizes and refresh rates. Ensure that your dataset does not exceed these limits.

 

Example:

– Check the dataset size and compare it with the Power BI service limits.
– Consider splitting large datasets or optimizing your data model.

 

 Step 7: Examine Scheduled Refresh Settings

 

Scheduled refresh settings can impact the refresh process. Verify that the refresh intervals and timings are set correctly.

 

Example:

– Go to the dataset settings and review the scheduled refresh configuration.
– Adjust the refresh schedule if it conflicts with other system processes or maintenance windows.

 

Step 8: Monitor Service Health

 

Occasionally, Power BI service issues can cause refresh errors. Monitor the Power BI service health dashboard for any known issues or outages.

 

Example:

– Visit the Power BI support page to check for any service advisories.

 

Step 9: Contact Support

 

If you have tried all the above steps and the issue persists, contact Microsoft Power BI support for further assistance.

 

Example:

– Submit a support ticket through the Power BI support portal with detailed information about the error and the steps you have taken.

 

Conclusion

Troubleshooting Power BI data refresh errors can be a complex process, but by following this step-by-step guide, you can systematically identify and resolve the issues. Remember to start by understanding the error message, then proceed to check credentials, data source accessibility, query changes, gateway configuration, dataset size limits, refresh settings, and service health. If all else fails, don’t hesitate to reach out to Power BI support for help. Keeping your data refresh process error-free is crucial for maintaining the accuracy and reliability of your business intelligence insights.

 

Live Power BI Support Transcript Example:

Below is a live transcript of one of our Power BI experts helping a customer to troubleshoot Power BI data:

Full Conversation:

Customer: This is your, is that what you’re trying to call me? I don’t know if it is hang up because we’re talking now. Okay. Okay. Oh yeah, that was your Anchorage phone number, right? Okay, yeah. I’m not out of Anchorage, but yeah, that was my number there. Awesome, awesome. Yeah, so let me tell you a situation, maybe you can help me out here. So I just rolled off a consultant project, and I made a Power BI dashboard with a data file for my client. And I wrote off the project last Friday, and he started updating the data, all that stuff. And now he’s getting all this weird, like, you know, let me see, I can see what happens. Refresh data source, you know, he was just trying to apply changes, you know, to the new file. And then he gets a couple of errors, like load was canceled by error in loading a previous table. That’s a typical one. And another one is like OLEDB errors and stuff like that.

Technician: I may or may not be able to help you with all these, but I can certainly help or try here. So if you’re comfortable with that, we can move forward here.

Customer: Yeah, yeah. Do you want me to send you these files by chance? Pardon me? Can I send you these files? Could you show them on your screen?

Technician: Yeah, yeah. Let me, yeah, let me get that. Let’s do that. Yeah. Let me relocate to my desk. Okay. Give me a minute.

Customer: Oh, gotcha, gotcha. I should talk to you while hanging out in my freaking bed, but we got to change that.

Technician: Oh, gotcha, gotcha. Because I was trying to fix this and all of that. And the thing is that I just left the company, you know, that I was consulting him in, but I told him, hey, if there’s any issues that you can help me out, that you need help, please give me a call. So, once again, he gave me a call. It’s like, hey, Nick, I tried updating the Power BI file and now it’s messed up. And it’s like, what did you do, man?

Technician: Yeah, I understand. Let’s take a look at the files and see if we can figure out what’s going on.

Customer: Okay, so I have that. Okay. Okay. I have, let me make sure I have this turned off. Okay. Do you want, okay. So let me load up. Here. Here, let me load up my screen. Sorry about that.

Technician: No problem. Take your time.

Customer: Oh, no, it’s fine. It’s fine. Yeah. So I’ll load up Power BI. And let me share my screen, okay?

Technician: Alrighty. And how do I do that here? How do I?

Customer: Yeah, so on the big gray area, if you kind of hover your mouse over that.

Technician: Yeah.

Customer: The entire screen is usually best.

Technician: Oh, shared entire screen. Okay.

Customer: Yeah. That way, if there’s any pop-ups, they, I can see them.

Technician: Okay. Entire screen. Okay. Cool. Do you see my screen?

Customer: Cool. I can see it.

Technician: Okay. So this was, let me show you the dashboard. And let me turn this off to reduce bandwidth. Okay. You see here, this is what I made for my client. You see?

Customer: Alrighty.

Technician: Okay. All this stuff here, industrial, environmental, all this fun stuff here. This was the files. This was the data set that he, that he was updating.

Customer: Okay. Gotcha. Gotcha.

Technician: All that stuff. Okay. And now I’m going to load you this file here. Is this a source data for?

Customer: Yeah, this is a source data. This is a source data.

Technician: Oh, there’s only one source data.

Customer: Yeah, there’s only one. He was playing around with my source data. That was what was going on.

Technician: Gotcha. Gotcha. Yeah. He’s loading this guy up right here. Oh my God. It takes forever. Enable editing. You see this thing takes forever. What the, why is it loading up again? Jesus Christ. Oh, why is it too loaded? That’s just bullshit.

Customer: Okay. You see here? It’s, this is all the data file, all this stuff, you know?

Technician: Okay. Yep. So he’s just got different tables or spreadsheets.

Customer: Yep. So I’m now trying to transform data, you know, data source settings.

Technician: Yep. Yep. And it’s, you know, his name.

Customer: Yep. So we’re changing the source.

Technician: Alright. Obviously. Browse, because that was the source original. And now we’re going to pick this guy right here. Okay. And then obviously confirm data source settings. All looks good so far.

Customer: Yep. Yeah. And this is what happens. Apply changes.

Technician: Yep. So technically it should be update okay, but this is what I’m getting.

Customer: Gotcha. Gotcha. Do you have his original file like that? Excel file?

Technician: Yeah. This was his Excel file. This was his Excel file. He said, Nick, I tried changing the Excel file. Now it doesn’t work. I’m sending you everything. Can you fix it?

Customer: Okay. And technically I can’t ask my company for help anymore because I left the company on Friday. You see?

Technician: Gotcha. Gotcha. You see?

Customer: Gotcha. Gotcha. Okay. So let’s go to, let’s start, close this out and we’re going to take a look at the data sources behind this. So if you can write on, nope, on the right-hand side there, you’ve got a list of the different tables, bank, cash, whatever. Right-click any one of those and hit edit.

Technician: Edit.

Customer: Edit query.

Technician: Okay. Let’s go for sales rep and edit query.

Customer: Yep. Okay. So we’re going to kind of drill back into these and try to figure out what the heck has gone on.

Technician: Yep. And so this one is awesome. It looks like invoice rep on the bottom, on the left-hand side. You’ve got some issues there.

Customer: Yeah, I see that.

Technician: Yep. So we click on that guy, but it actually looks like it’s okay. Click on the next one. This is actually looking okay. Keep clicking the others. Why are you giving us fits there, Power BI?

Customer: Yeah. Keep going.

Technician: Okay. All right. Then let’s go to in the ribbon, hit refresh preview. Let’s see what happens.

Customer: Ribbon, refresh preview, this one?

Technician: Yeah, this guy, yep.

Customer: Okay, refresh preview. Nothing happened.

Technician: I’d expected to have done something for you there. Click the down arrow on that refresh preview. Oh, let’s click data source settings. Maybe it didn’t pick up on your new data source settings.

Customer: Yeah, it’s right there.

Technician: Okay, that’s it. Okay. Hit close.

Customer: Close.

Technician: And hit refresh preview again.

Customer: How about refresh all?

Technician: Nothing.

Customer: Okay, on the left-hand side of your ribbon, close and apply.

Technician: Apply changes?

Customer: Apply changes, yeah.

Technician: Oh, God. Wow. Just a second here. Customer profit. I don’t even see those tables. Sales rep. Oh, sales rep appears there. Okay. Move canceled by an error in a previous table. Scroll down again, if you would. Unexpected exception occurred. Yeah, it’s strange. Yeah. I mean, he was just adding and editing new information, data, new information. That was all that was going on. Yeah. Hit close. And let’s go back into where we were at just a minute ago, the edit your data sources there. So on the right-hand side, pick one of the tables and hit edit.

Technician: Yeah. Edit query?

Customer: Yep, yep. And this is where you actually write the query that pulls all the data in. The other part is kind of transforming the data once you have it.

Technician: Yeah, yeah. So spur AR, I think, was one of the – customer profit actually was one of them that there was an issue with.

Customer: Yep. So now that we’re on that one, on the right-hand side, you’ve got applied steps. Hit source.

Technician: Source, okay.

Customer: Yep, click, yeah, that’s a great spot to click on that. And is that pointing to your new data source?

Technician: Yep, it is.

Customer: Okay, just say okay.

Technician: Okay.

Customer: And then hit navigate.

Technician: Navigation, yeah.

Customer: Yeah, no, that’s perfectly fine, promoted headers. Yeah, just kind of scroll down. This is like a step-by-step.

Technician: Okay.

Customer: Just say okay.

Technician: Okay.

Customer: Don’t worry about that.

Technician: Okay.

Customer: Okay, and then change type.

Technician: Yeah, so that’s – this is – everything is showing me that everything is looking good. Do you have another copy of this – that Excel spreadsheet?

Customer: No, I don’t right now. This is all he has.

Technician: Tell you what, make a copy of it if you would, please. Copy of this?

Customer: Yeah, just make a copy of it. We’re going to play a game with it.

Technician: Okay. Copy.

Customer: And then make sure you close out of both those files. It shouldn’t cause – yeah, it shouldn’t cause you any fits, but just in case.

Technician: Okay. I’m going to close.

Customer: Okay. It won’t close, man. All right. Now go to data source settings in the ribbon and point to the new copied data source.

Technician: Okay. And say okay.

Customer: Yep. And close.

Technician: Yep, yep. And then on the left-hand side where it says close and apply, put the down arrow on that.

Customer: Yeah, and hit apply.

Technician: God damn it. Oh, it is here. The number has blank values. You see?

Customer: How is it blank? So actually you can check that out on that drop. Go back to where you’re at on the number field there.

Technician: Number field there?

Customer: Yeah.

Technician: And then there’s a little triangle on the right-hand side.

Customer: Huh?

Technician: You’re breaking up. You’re breaking up. Hang on here. Can you hear me now?

Customer: Yeah, I can hear you.

Technician: Okay, now I can hear you. Okay, excellent, excellent. Okay. So did you find any that were blank there under number?

Customer: No, they were not blank. None of them were blank.

Technician: Ah, shoot. It’s weird. They say it was blank, but none of them were blank. Yeah. Hit column profiling based on – so on the bottom left, there’s a column profiling based on way at the bottom, way, way, way at the bottom.

Customer: Yeah.

Technician: Yeah, click on that and do the entire data set.

Customer: Okay.

Technician: And so let’s do another – let’s do one other thing here. In the – pick your view ribbon.

Customer: View ribbon, okay.

Technician: And hit column quality and column – put the check marks for column quality, profile, and distribution there.

Customer: Like that?

Technician: Yep. And so that one shows that you’ve got a whole bunch of empty numbers in there.

Customer: Really?

Technician: Yeah, which if you know that those are kind of BS, we can get rid of them. It says – yeah, basically, yeah, what it says in that row is 57% are valid and 42% of them are empty. And it only pulls in a few of them at a time. You’ve got 50,000 or so probably.

Customer: You would be right. All these are numbers. I can look at the number itself.

Technician: Yeah, take a look at your file there, if you would, please.

Customer: Yeah, I mean, I normally ask my company for this one, but consider I left the company.

Technician: I know, yeah.

Customer: I can’t ask them for help anymore.

Technician: Exactly, exactly. So is this in an Excel table or not?

Customer: Excel table, yes.

Technician: Okay, good. Yeah, scroll to – yeah. Maybe sort those by the number there.

Customer: Right here?

Technician: Well, actually, there’s only 1,200 of them, isn’t there?

Customer: Yeah.

Technician: Okay. Okay, let’s go back to the Power BI.

Customer: Okay.

Technician: Yep, and click on source on the right-hand side of the applied steps, the very first applied step. Click on the source there.

Customer: Yep.

Technician: And then so basically we are pulling from that, which is great.

Customer: Okay, say okay to that.

Technician: And then the navigation, the next step down, you’re pulling in the sheet of items from the –

Customer: Yeah.

Technician: Okay. Is this Power BI file saved someplace? I want to do something, but I don’t want to break it entirely.

Customer: It’s saved, yeah.

Technician: Okay, okay. I have a backup here from my old client right here.

Customer: Okay.

Technician: Yep, so I’ve got that.

Customer: Good, good, good. So under the source, we’re going to change that around a little bit. And so – oh, no, those are all sheets. Okay.

Technician: What?

Customer: No, no, I’m just trying to – promoted headers, change types. Okay.

Technician: Rename columns. So under number, N-U-M, we’re going to get rid of all the blanks there.

Customer: Okay. Remove empty.

Technician: Remove empty, yep.

Customer: Okay, so now we’ve got 1,241 records there, which I think is what you should be seeing in your Excel file. Does that sound right?

Technician: Yeah, yeah.

Customer: Okay, good, good. Let’s see if that’s – okay. Let’s go back. That looks better. I mean, that probably addressed one of your issues there.

Technician: Okay. And then hit – under the close and apply, just hit apply. There might be a few different things going on. We’ll find out.

Customer: Yeah.

Technician: We got lucky. Did that work?

Customer: I think so. Just hit close and apply or close or whatever.

Technician: Close and apply?

Customer: Yep. Boom. It worked?

Technician: I think it worked. One more time. You want to try it one more time? Refresh?

Customer: Yeah, hit refresh. And point it back to your original file if you want.

Technician: Yeah, I want to save this as Nick Fix. Ready?

Customer: Yep.

Technician: Yep. Roll the dice. Spin the wheel.

Customer: Wes! Sweet! Yes, it worked!

Technician: Awesome, awesome. I was figuring it would be like a bunch of different issues. Thank God, man. Awesome, bro. Thanks, man. I really appreciate you helping me, my friend.

Customer: Oh, you bet. You bet. Hey, good helping you on this one. There’ll be a very short survey at the end here if you could give me kind words or unkind words.

Technician: Of course. You got it, man. Yeah, you’re helping me out, brother.

Customer: Alright. Hey, take care, man.

Technician: Take care.

Customer: Take care.

Technician: Bye.

Customer: Bye.