Privicy Error when refreshing files with combining tables: Error -2147467259

[danielpcamara]

Hi!

In a given query of dataset to my repoart, I combine data from a ODBC Sorce with an Excel File from SharePoint. Both has its privacy settings set to "Public" (I tryed "Organizational" too) and I still get this error:

Error Translation:

[Unable to combine data] {text} references other queries or steps, so may not have direct access to a data source. Recompile the data.

I did this procedure and still does not solve my problem:

https://community.powerbi.com/t5/Service/Problems-refreshing-files-with-combining-tables/m-p/1051616

Can some one help me please?

[v-janeyg-msft · Accepted solution]

Hi, @danielpcamara 

Are your data sources all from SharePoint excel? What's your data source?

Try to set the data source to public in SharePoint. Not the setting when connecting in powerbi.

According to the error message, it is judged that the data of the data source cannot be obtained on the service after you perform the combination operation. Do you have any complicated operations in PQ? It may need to modify your steps in PQ.

Reference:

Solved: Unable to Combine Data Refresh Error - Microsoft Power BI Community

Solved: Refresh failing (Combine Data) - Microsoft Power BI Community

Solved: Refresh problem - Unable to combine data - Microsoft Power BI Community

Solved: Unable to combine data - one server with multiple ... - Microsoft Power BI Community

Please upgrade the gateway to the latest. Try to public the file again.

Can the data source be connected to the gateway normally? Can you share the connection settings in gateway and settings in the dataset in the service? Refresh history?

Please hide privacy and change your browser language to English for my understanding.Thank you.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft · Accepted solution]

Hi, @danielpcamara 

Did the last scenario work? Is your problem solved? 

You didn't show all your queries, I can't analyze it well.

For the solution to the above thread, I can explain that he merged the custom query into a main query, and the refresh was successful. This may be because the service doesn't support the refresh of custom functions in some cases. Your problem should also be this, not your code error.

Like this:

Best Regards,
Community Support Team _ Janey

[danielpcamara]

@v-janeyg-msft, 

Can you refresh the report in desktop?

---

Yes.

And you can go to the data source to see if the data is public and not just at the powerbi level

---

Yes:

---

And check if the first option is checked in the gateway.

---

Yes:

Solved: New ErrorCode=-2147467259 - Microsoft Power BI Community

---

Since it updates in PBI Desktop, I don't think that this will help.

---

Run-time error-2147467259 (80004005) when you set a property of a chart - Office | Microsoft Docs

---

This Doc is for VBA and not M.

I need more details to judge.

 What else can I pass to you?

[v-janeyg-msft · Accepted solution]

Hi, @danielpcamara 

Are your data sources all from SharePoint excel? What's your data source?

Try to set the data source to public in SharePoint. Not the setting when connecting in powerbi.

According to the error message, it is judged that the data of the data source cannot be obtained on the service after you perform the combination operation. Do you have any complicated operations in PQ? It may need to modify your steps in PQ.

Reference:

Solved: Unable to Combine Data Refresh Error - Microsoft Power BI Community

Solved: Refresh failing (Combine Data) - Microsoft Power BI Community

Solved: Refresh problem - Unable to combine data - Microsoft Power BI Community

Solved: Unable to combine data - one server with multiple ... - Microsoft Power BI Community

Please upgrade the gateway to the latest. Try to public the file again.

Can the data source be connected to the gateway normally? Can you share the connection settings in gateway and settings in the dataset in the service? Refresh history?

Please hide privacy and change your browser language to English for my understanding.Thank you.

Best Regards,
Community Support Team _ Janey

[danielpcamara]

Hi @v-janeyg-msft!

Are your data sources all from SharePoint excel? What's your data source?

---

No, I have Sharepoint Excel and ODBC. Let me tell a little bit about my ETL. The {text} that you highlighted for me is the Query that has the ODBC Connection as the Source and is the step that make a Table.Combine with another Query that has as source the "SharePoint.Files" then a "Excel.Workbook" inside Sharepoint.

Another's transformations that occur in the Combine Table are:

---

Navigate (using [] and {})
Table.Buffer
Table.RemoveColumns
Table.TransformColumnTypes
Table.TransformColumns => each Text.From(_, "pt-BR")
Table.AddColumn => each a_number_X
Table.ReorderColumns
Table.AddColumn => each "a_text"

---

Do you have any complicated operations in PQ? It may need to modify your steps in PQ.

---

As you can see nothing fancy.

---

Try to set the data source to public in SharePoint. Not the setting when connecting in powerbi.

---

If you mean in the (app.powerbi site > workspace > dataset configuration > DataSource Credentials > Edit datasource) is already set.

Can the data source be connected to the gateway normally?

---

Yes, I made a test now by just deleting the last step of Table.Combine, and It loaded fine.

Can you share the connection settings in gateway and settings in the dataset in the service? Refresh history?

---

Since my test above works, I don't think that the gateway is not working. And my dataset is configure like this:

In the refresh all errors are the same that I pass in this post:

---

Solved: Unable to Combine Data Refresh Error - Microsoft Power BI Community

---

I'm really trying to avoid using DAX to Solve, since my dataset is big.

---

Solved: Refresh failing (Combine Data) - Microsoft Power BI Community

---

I'm not using Scheduled refresh.

---

Solved: Refresh problem - Unable to combine data - Microsoft Power BI Community

---

It Works, solved. Since this is a little bit long. Can you please replay again, but this time with just this link? I will accept as an answer. This way if someone stumble upon this post, the answer will be more clear. Thanks a lot @v-janeyg-msft.

[v-janeyg-msft · Accepted solution]

Hi, @danielpcamara 

Did the last scenario work? Is your problem solved? 

You didn't show all your queries, I can't analyze it well.

For the solution to the above thread, I can explain that he merged the custom query into a main query, and the refresh was successful. This may be because the service doesn't support the refresh of custom functions in some cases. Your problem should also be this, not your code error.

Like this:

Best Regards,
Community Support Team _ Janey

[danielpcamara · Accepted solution]

Hi, @v-janeyg-msft.

Just to be more clear, this solve the problem:

[v-janeyg-msft]

@danielpcamara

That's right.  You can accept answers that are helpful to you as solution. Thanks.

Janey

[v-janeyg-msft]

Hi, @danielpcamara 

Can you refresh the report in desktop? This is important.  And you can go to the data source to see if the data is public and not just at the powerbi level. And check if the first option is checked in the gateway.

Solved: New ErrorCode=-2147467259 - Microsoft Power BI Community

Run-time error-2147467259 (80004005) when you set a property of a chart - Office | Microsoft Docs

I need more details to judge.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[danielpcamara]

@v-yingjl , Sice you help bi_babel-azza in the other post, can you help me too?