Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

32 REPLIES 32

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.

I have the same problem, and I think it was due to the step of Combine Files for the files in the SharePoint. The report in Power BI Desktop works fine. We just can't refresh the dataset after the report is published. My workaround is to create a table with the Combine Files using Power Query, and use this table as a new data source. I now can refresh my new dataset after I publish the report.

Anonymous
Not applicable

Hi @sukajalan2

Where are you creating this tabel / where is saved? In Power BI or do you create a local tabel and save it On SharePoint?

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

I ran into this issue aswell, the excel file I connected with uses certain formatting.
Using a export to a CSV file instead of Excel solved this issue for me.

Anonymous
Not applicable

Well this is not a solution of the problem. This is "going around the problem"

The issue still exists.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors