We cannot convert the value "[Binary]" to type Binary. Table: Query1 - Merge issue

[Maddy99]

a couple months ago and this week i got a strange bug on Power BI service with the data refresh. We cannot convert the value "[Binary]" to type Binary. Table: Query1 it took me a long time to see what was going on. this error only occur on power bi service. on power bi Desktop everything works fine. i end up with delete step by step in power query to reproduce the error. this way i found out that when i delete the merge from the table the refresh works fine. there was something strange with the merge function in power query. i reproduce it in a separate file, quick and dirty style, to test it and there the merge works fine. step 2 was to complete rebuild the table exactly like the original and it got the same error. what is going on: i used a 3 merge layer. table 1 is merged with table 2. and then i merge table 2 with table 3 (query1). and this cause the error. when i remove the first merge (table 1 to table 2) the refresh in power bi service works again. i have no idea what to do about it other than works around it. but i am happy to know what the limit are. source: query1 - sharepoint folder (excels) table 1 and 2 - Sharepoint lists.

[v-qiuyu-msft]

Hi @Maddy99,

I'm trying to reproduce the issue on my side but unfortunately not. For your issue, there are some tips which you can try: 

1. When you use Sharepoint folder data source, please use the Combine Files command (instead of just adding a Excel.Workbook command). 

2. Please try to use Web data source to connect to Excel stored in SharePoint online instead of using SharePoint Folder data source follow this article: https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links

Best Regards,
Qiuyun Yu

[Maddy99]

Hoi @v-qiuyu-msft. i try your solution but unfortunately it does not work for this case. what we do here is that we store a new file for every quarter in the folder. and to combine those file to 1 table we use the intern power bi loop. also the problem lays not in the loop but in the additional merge. if i delete the merge from table 1 to table 2. than the final merge from table 2 to the query works fine. do you maybe have some other ideas? mvg, madelon

[v-qiuyu-msft]

Hi @Maddy99,

I would suggest you create a support ticket to let engineers look into the issue on your side. 

Best Regards,
Qiuyun Yu 

[tonymaclaren]

I have the same issue and spent 6 hours over several days with a Power BI Engineer.They have a workaround involving using the personal on prem gateway, but it did not work for me. In anty case the whole reason I designed the model to be stored on sharepoint is so a gateway is not needed and it is miles better than onedrive which is personal. If that person leaves you have a problem. Also the URLS are diferent so you need to paramaterize the source if colaborating on developement.

Anyway. Microsoft eventually told me that this is an architectural issue concerning privacy levels.You can prove this yourself by setting the global privacy levels to private and the same query will fail in the Desktop.They further told me that a great many customers have thisproblem and it will be fixed in a future release.This better happen soon as my only solution currently is to refresh by republishing the dataset every day!!