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

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

DevadathanK — Tue, 31 Aug 2021 05:46:23 GMT

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!

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

xzmiche — Wed, 01 Sep 2021 01:30:55 GMT

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

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

Anonymous — Wed, 01 Sep 2021 07:52:58 GMT

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

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

v-kelly-msft — Thu, 02 Sep 2021 09:12:07 GMT

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-Binary/m-p/210034

Best Regards,
Kelly

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

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

DevadathanK — Fri, 03 Sep 2021 09:57:07 GMT

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.

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

DevadathanK — Fri, 03 Sep 2021 10:02:46 GMT

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.

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

DevadathanK — Fri, 03 Sep 2021 14:04:50 GMT

Hi Anonymous 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.

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

DevadathanK — Fri, 03 Sep 2021 14:03:12 GMT

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

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

Anonymous — Fri, 03 Sep 2021 14:05:50 GMT

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

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

Anonymous — Thu, 07 Jul 2022 07:13:00 GMT

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

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

Zrt99 — Thu, 15 Sep 2022 16:39:18 GMT

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.

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

Anonymous — Mon, 30 Jan 2023 07:53:51 GMT

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.

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

powerbicastelr — Mon, 22 May 2023 15:21:20 GMT

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.

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

LeeeBecker — Tue, 20 Feb 2024 13:57:21 GMT

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!

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

Senobar — Sun, 23 Mar 2025 19:55:03 GMT

I’m experiencing the same issue.

Two of my resources are .csv files stored in a SharePoint site, and another resource is a Microsoft List located in a different SharePoint site. Instead of transferring all the resources to a single site, I found a workaround that worked for me! Here’s what I did:

Let’s say your last step before the merge step is named X.

Between X and the merge step, add these two steps:

1) Redundant step: Y = X (this step does nothing just reference the last step before this step)

2) Condition step: Z = if true then Y else X (This condition essentially does nothing.)

After step Z, reference this step and continue with your workflow.

I’m not entirely sure why this works, but I discovered this workaround by chance. If anyone knows the reason behind this, please let me know!

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

Stephen_Sink — Wed, 09 Apr 2025 22:37:36 GMT

In case this ever helps someone in the future.

I had this exact same error/issue. The solution ended up being aligning the privacy levels in the data connection setting in the workspace to "organizational" for both. I had two connections to two different Sharepoint sites - one with "Private" privacy level and one with "Organizational" privacy level. I changed the Private one to Organizational by going into the workspace, going to the Lineage view for the report, click on the data sourc which opens a right hand panel, which contains a "Go to settings" link, and changed it there.

After that I was able to refresh without issue!

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

Eduardo_Suela — Thu, 18 Sep 2025 09:57:52 GMT

There is no error in your data

The solution above works for me and many others

https://community.fabric.microsoft.com/t5/Power-Query/Power-Query-Merge-Append-quot-Expression-Error-We-cannot-convert/m-p/3711240/highlight/true#M121976

It might be Power Query trying to do smart things on Sharepoint Excels. Smart things that have a bug inside.

When separating the query in halves, it works, so the problem is definetily in power query