Power Query Merge/Append "Expression.Error: We cannot convert the value "[Binary]" to type Binary."

[DevadathanK]

Hello Community!

I'm facing an issue in Power Query and can't seem to find a reason to why its happening. I could really use some advice on the reason to the problem. 

This is the error I'm getting  -

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

When am I facing it? - When trying to merge or append 2 queries. 

What are the data soruces of the two queries? - 2 Excel files kept in folders in 2 different SharePoint sites. The privacy of both are kept as 'Private'. (I've also faced this issue while trying to merge an SQL query with an Excel) 

What are the datatypes of the columns I'm using to connect the tables? - Both are of type Text. (Also no Blank values are present in both columns).

Known Work Around - Now, I have no idea why this error is shown, but I found a work around to this which is using 'Merge as New Query' or 'Append as new query'. If I do that, then this error is not shown and previously when I faced this issue, I did that.

However now, I need to do 3 different merges on this table and if I use this work around method, I'd be making more and more tables which I dont really prefer now (Its getting really clustered/crowded by tables). 

I just really want to know the reason behind this and find a proper solution once and for all. I have done other merges/appends on other tables in this file and havn't seen this error. So, I'm not sure why its popping up for some. 

If you have knowledge on this, kindly help me! I'd really appreciate it!

[LeeeBecker]

This is so strange.  I have had the same problem after changing the source data to Sharepoint.  I decided to split the query into 2 so that I could see the data at the point of it breaking.  That was literally all I did, but now it works!

[Zrt99]

I can relate this with O365 password change. This affected 25% of all my reports published on Power BI Service.
If the report was refreshed from Power BI Desktop there was no error.  
Why: unknown
Solution that worked: in Power BI Service / dataset settings  / Edit Credentials - click on each to to update credentials.

[powerbicastelr]

This solved it for me. I think I saw it in another one of the replies. 
I checked my credentials/sign-on settings. One of the SharePoint connections had a setting of "Private" while the other one had "Organizational." 

For me, both are in the same parent SharePoint, but different subfolders. Changing the "Private" to "Organizational" sign-in solved the issue. 

[Anonymous]

If you are facing this problem in Dataflow:
Go to Project details and mark 'Allow combining data from multiple sources...'.

[lukmtb08]

Solved my problem - thank you! 🙂

I had the issue within a dataflow, merging data from two excel files which are stored in the same SharePoint. 

[watkinnc]

If the Privacy Level of both are set to "Private", then perhaps it's not allowing you to combine files from two different locations into the same source file.  I would try changing the Privaly Levels to "Always Ingnore Privacy Levels".

--Nate

[DevadathanK · Accepted solution]

So, I moved one of the excel files to the SharePoint where the other file was being stored and this error is not shown anymore.

From this, I'm assuming that its an issue (a compatibility issue of some kind) between the two SharePoint sites, as switching them solved the issue. Like I stated before, the privacy levels of both are set to 'Private' and the only difference that I could see is that in one SHarePoint site i'm a Member and in the other one, I'm the owner. Not sure if this is the issue. 

So, work around 2 - Store the files within the same SharePoint site
Reason for the error? - Still a mystery

[v-kelly-msft]

Hi  @DevadathanK ,

I found an old thread showing a solution,you may have a try:

https://community.powerbi.com/t5/Service/Underlying-error-message-We-cannot-convert-the-value-quot-B...

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[DevadathanK]

Hi @v-kelly-msft, Thank you for replying!

I had seen this post before when I went through any material I could find to get the solution, I couldnt understand the solution offered in this post and futher more alot of comments were stating that its still a work around. I want to know why this problem occurs. 

Currently I was able to find yet another workaround by storing the files on one single SharePoint site. However in a case where the files are stored say, in an SQL and Excel in SharePoint site, then such work around wont be helpful.

[watkinnc]

Try transforming the list of Binary Columns first, or if you have them in separate queries, Transform on each query first. If you have them in a list already, then it's:

List.Transform(NameOfBinaryColumn, each  Csv.Document(_, [Encoding = 1252]))

Otherwise, just use =Csv.Document(NameOfPriorStep)

--Nate

[DevadathanK]

Hi @watkinnc Thanks for replying to me,

The way I connected the table is by using the 'Combine files' option present for the Binary data type column for the file. Then the automatic Transform queires are created and then I get the data in a table format. Its after this that I tried to merge the two tables. 
 I'm not certain if this method relates to this way, Let me try this method out. 

I tried moving one of the excel files to the SharePoint site where the other one is being stored and now the error is not shown. I literally just copied the exact same file to the SharePoint site (no others whatsoever). Still have no clue as to why this issue is shown. 

[xzmiche]

Maybe this is caused by some data error in your Excel files. Would you please share some data for further testing?

[DevadathanK]

@xzmiche Thanks for replying!

I don't think its an issue in the excel files. I had the files kept in different SharePoint sites earlier, now I moved the file itself to one unified SharePoint site and now it works (All I did was move the same exact file to one of the 2 SharePoint sites, no other changes in or on the file).

It's some issue when the file is being stored in the different SharePoint sites, I dont know why still.