Refresh attempt failed due to this error?

[patbyr]

I tried to figure out what is causing this error to make the refresh failed but I'm struggling to understand what exactly is the cause so I can fix it. Please see the error code below and any help would be great.

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"<pi>SharePoint: Request failed: The remote server returned an error: (429). (429 TOO MANY REQUESTS)</pi>"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"https://truemfg.sharepoint.com/sites/Operations"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.SPRequestGuid","detail":{"type":1,"value":"a99123a0-50a8-c000-d66e-38ca22631d83"}},{"code":"Microsoft.Data.Mashup.ValueError.Url","detail":{"type":1,"value":"https://truemfg.sharepoint.com/sites/Operations/_api/Web/Lists(guid'16fef537-676d-415e-a3d4-dfc615f2...')/Items(804)/AttachmentFiles"}}],"exceptionCulprit":1}}} Table: 3s Audit MFG.

[arvindsingh802]

Please break your model in parts
1. Load data into dataflow without doing any transaformation (Raw Data)

2. Use the dataflow as source in your dataset/dataflow and here you perform all your required transformation
This will help reducing number of requests to SharePoint.

[v-henryk-mstf]

Hi @patbyr ,

As @lbendlin  said. Are you performing too many on-demand refreshes while configuring scheduled refreshes, resulting in too many refresh requests being sent to the data source. Is it possible to provide information such as screenshots of the refresh history?

Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[patbyr]

Here is the screen shot of it per your request. I hope this is what you are looking for.

[lbendlin]

I don't think your schedule is the issue.  Most likely the data source queries inside your dataset are causing the issue.  You can use Fiddler or similar to monitor network traffic when you do a refresh from Power BI Desktop.  That will give you an idea how many requests Power BI pushes into the Sharepoint server.

The call limits are specific to each Sharepoint farm. Talk to your Sharepoint Admin about what's configured for your instance.

[patbyr]

Here is another error code. I’m not that great on reading these error codes but hope someone can help me out with this. Also, I can try to explain on what I have it set up to do. I have leadership on the floor to submit 3s audit through Microsoft Form. There may be like total 10 – 20 plus submission per day. I have the power automate to pull every respond to True Operation 3s Audit SharePoint page. They do submit attachments such as photos or videos through the same form and it’s being storage in the same SharePoint. I have this 3s Audit Power Bi pulling information and links from that same SharePoint to provide reports so any leadership can have access to it. They can even click on the links in Power Bi to see the attachments that are storage in the same SharePoint. Also, we have the power bi to set up auto refresh every hour but now we dial it back to refresh every 3 hours. I hope this helps or make sense but please let me know.

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"SharePoint: Request failed: The remote server returned an error: (429). (429 TOO MANY REQUESTS)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"https://truemfg.sharepoint.com/sites/Operations"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.SPRequestGuid","detail":{"type":1,"value":"128b25a0-d052-c000-c3c8-5a6cd3e1b720"}},{"code":"Microsoft.Data.Mashup.ValueError.Url","detail":{"type":1,"value":"https://truemfg.sharepoint.com/sites/Operations/_api/Web/Lists(guid'16fef537-676d-415e-a3d4-dfc615f2..."}}],"exceptionCulprit":1}}} Table: 3s Audit MFG

[lbendlin]

Still the same thing 

429 TOO MANY REQUESTS

You are still bombarding Sharepoint with more requests than it is willing to handle.

[patbyr]

Thanks for the direct info but again I'm sorry this doesn't really help me to understand what exactly I need to do to fix it or adjust. Also, do you know how many request does SharePoint can handle? or is it speed thing that server can't keep up?

[patbyr]

We are currently using premium license, but we set up this one report to do an auto update every 1 hour. Not sure if that may be too much but also, can you please explain why it's showing 429 requests. Would like to understand what causing it so I know moving forward what to do or not to do along with possibility fix it on our end.

[lbendlin]

This has not much to do with either your Premium license or the hourly refreshes.  Your issue is that your data source refuses to cooperate because you have sent too many requests its way. Talk to whoever maintains that Sharepoint farm.

[v-henryk-mstf]

Hi @patbyr ,

According to this problem, refer to the following blog to see if the solution is helpful to you:

Power BI On-Premise Data Gateway Error: DM_GWPipeline_Gateway_SpooledOperationMissing","parameters":...

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[patbyr]

I'm not sure what exactly is going on but I am struggling to understand why this is happening. From what I'm seeing here is that it is still doing the updates but some of it goes through while some others pop up with an error. Here is the thing, the error code isn't the same one everytime. It's always popping up a different error code everytime. Not all of it are the "429 TOO MANY REQUEST". Please see below where you will find the screen shot of the refresh history along with the most recently error code.

{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The credentials provided for the SharePoint source are invalid. (Source at https://truemfg.sharepoint.com/sites/Operations.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceOriginKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceOriginPath","detail":{"type":1,"value":"https://truemfg.sharepoint.com/sites/Operations"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"https://truemfg.sharepoint.com/sites/Operations"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"AccessUnauthorized"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"SharePoint\",\"path\":\"https://truemfg.sharepoint.com/sites/Operations\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"AccessUnauthorized"}}],"exceptionCulprit":1}}} Table: 3s Audit MFG (2)

[lbendlin]

You seem to have a Sharepoint instance that is in general struggling to keep up with user requests

"value":"The credentials provided for the SharePoint source are invalid. (Source at https://truemfg.sharepoint.com/sites/Operations.)"

That's an euphemism for "I'm stressed out, someone help me" 

[patbyr]

Here is what I'm thinking but I haven't try it yet. I'm thinking about setting this up in a different way due to the fact that I have this Power Bi set up where to update the whole SharePoint into Power Bi which maybe why this is happening. We don't need to update the whole SharePoint into Power Bi since it's already there while we only need to update the new ones going into it. Here is the video that I found in youtube which is what I'm thinking about setting up.

https://youtu.be/jmJJHFGwWFc

What do you think?

[lbendlin]

I should have asked that in the beginning - what are you trying to accomplish?  Are you using a Sharepoint.Files() or Sharepoint.Contents()  connector?

Can you show a sanitized version of your Power Query?

[patbyr]

I'm going to try  my best to explain on what I have and what it's doing. Below is the screen shot of my power bi report. In the middle of the report, you will find attachment links. I'm pulling the links on where they are from SharePoint. I have this power bi pulling information straight from SharePoint which is where I have people submitting their Microsoft form. In the form has a question where to add attachments. So any attachments add to the form will go straight to the SharePoint. (Form -> SharePoint -> Power Bi)

[lbendlin]

Weelcome to the 429 club.  You have bombarded your data source with too many requests.  Slow down, or upgrade your license.