cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Underlying error message: We cannot convert the value "[Binary]" to type Binary

 

Hi, everyone,

 

I have a two data sources one by a gateway and a several Excel files (with the same structure) from SharePoint. All the excel files are coming in i Power BI as one tabel. The repport wich was made in power bi desktop runs well and i can refresh the datasource without an error.

 

The repport is published on power bi service and I receive the following refresh error:

Can someone suggest a solution? Someone how had the same problem?

 

PowerBI error.jpg

1 ACCEPTED SOLUTION

@AnonymousI created the the table in Excel (Power Query) and saved it in SharePoint.

View solution in original post

24 REPLIES 24
nishantjaincent
Advocate IV
Advocate IV

For anyone who is still having this issue, I found the reason for the error to be the "Privacy level" setting in Power BI service. You need to make sure that all your connections where you are appending or merging data have the same privacy level. 

 

This also explain why it would work in Power BI Desktop and not in Power BI Service as they can have different settings.

 Settings.png

Every time I have this error, I check the privacy level of all the credentails and I find that in one of the setting have either changed or not set to begin with

 

Hope this helps others
Nishant

Thanks - fail to comprehend why PBI changes authentication specially in the service. Most confusing as we have 20+ Sharepoint files and folders all using the same authentication and the failure occured with different files/folders.

I believe this is because the credentails expire after certain time (my guess is 3 months). In your case, the credentials to the connection of your files might have been created at different point, suggesting the failure in connection of these at different times. Not sure how true this is though, it's just a guess

Thank you. I solved my issue by applying this method!

bourassi
Regular Visitor

Hi,

We have the same problem with refresh error on Power BI service, but working well on desktop:

Error message : "We cannot convert the value "[Binary]" to type Binary"

Bests regards,

SwedeJones
Frequent Visitor

I have the exact same problem too!

I load excel files from 2 different SharePoint folders. I then do reference this to a new query, which is not a solution to me apparently... I combined the binaries using the Excel.Workbook() formula to get the Excel files' contents. It works great in PBI Desktop but not by schedule refresh in the service. Annoying!!

Anonymous
Not applicable

I have the same problem, there issue is not actually resolved. 😞

 

 

Update: i came across this issue, that is almost the same as mine. ---  https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-so-it-may-n...

 

and there, Mat provided a link to this blog with a solution that actually worked!

Ken Puls article -- https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

 

i hope this will help you too.

More than a year later struggling with the same problem and riri-s suggested link helped with workaround, thanks!

Coline
Frequent Visitor

Hello,

 

I have exactly the same issue.

I didn't find the way to get through this issue.

 

I have 2 type of Data source : Excel File on 365 and Sharepoint Folder.

I have 2 Sources "Sharepoint Folder" to have 2 different tables and I Combine these 2 tables at the end.

My solution was to create a Power Query report combining the files, upload to Power BI Service, schedule the refresh, and use it as the data source for Power BI report.

Anonymous
Not applicable

Hi @sukajalan2 

 

I am digging up this subject because I have the same issue and your solution interests me a lot but I don't know how to load an excel power query report on my power bi service

could you help & explain ? 

 

thx !

@Anonymous  I save the .xlsx file in SharePoint and use it as the data source for Power BI report. I hope this helps.

Anonymous
Not applicable

Yes, thanks for your quick answer !

 

but did you schedule the query refresh of the excel file or is it still a manual action ? 

 

it is manual.

for automation, you can use macro to convert xlsb to xlsx (basically, open the xlsb file and save as xlsx).

Hello!

 

Thanks for your answer.

When you speak about Power Query report, it's on Excel?

Yes, it is using Excel.

Hello!

I find the issue : For the final generate table in a "Sharepoint Folder" source, you can't merge with another table!!! I don't know why.

I needed to merge with a DATE table.

So I did a reference from the table genrate by the "Sharepoint Folder" source, did the merge in the new table  and it works!

Mystery!!

v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

>>I have a two data sources one by a gateway and a several Excel files (with the same structure) from SharePoint.

Since I' m not so sure for your issue, can you share more detailed information about this?


For instance:

Which file you host on gateway?
which type of sharepoint you used?(on premise or online)

 

In addition, did you use custom function to merge these excel files? According to the error message, it seems like the issue is related to power query formulas.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thank you Xiaoxin Sheng that you make a lite time for my question.

 

I have a gateway connection for a Sql database and a SharePoint online connection.

 

I think also that the problem is from the SharePoint connection. I have used a SharePoint Folder connectivity.

In the source query I received a table with all the documents that inside the SharePoint page. In this table i have a "content" column with "binary" text in each row. This table a have filtered to have all the Excel files needed for the project and that I have oppened the merged content and in this way a have created a big table with all the information for every Excel files.

 

I don't  understand why the refresh is possible with power bi desktop and i have trouble with the published report in power bi service.

 

Best regards,

Barni

Hi @Anonymous,

 

I test to get data from sharepoint online and follow your steps to publish to service, it works on my side.


Have you test on the last version of power bi ?
If you create a new report which original datasource, can you reproduce it?

 

In addition, I think the issue may related to custom function which used to convert your data in power query.(current custom function only available on desktop)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors