SharePoint/Service refresh errors Can not convert type binary to type table

[Dellis81]

Hello (again)

I continue to struggle in getting the Service refresh to work with SharePoint connections.    I will note, at the desktop level, data model and refreshes are working as expected.   The challenge is at the Service refresh.   Interestingly, when I click the refresh a second, third, time, etc - each time a differing table is indicated having a problem.   I've gone thru up to 20+ manual refreshes, with probably 17 of those being unique queries throwing the error.   And with each refresh attempt, the error may be thrown anywhere between 30 seconds to 15 minutes of initiating the refresh.

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Message: We cannot convert a value of type Binary to type Table. Table: InventoryScheduleTable.
Cluster URI: WABI-US-NORTH-CENTRAL-B-redirect.analysis.windows.net
Activity ID: e6b10d96-75fe-41a2-b764-aa1d4758e198
Request ID: 2c2f1b6a-e056-3d06-7796-486f885bb89e
Time: 2020-01-30 02:17:23Z

As I search various forums - the most common solution is a mismatch of data types in a column expand function.   In this particular query - there is no expand.    But, I'm sure there are "expand" functions in hte chain of queries getting to the source "Subheader"

As to show the simplicity of the InventoryScheduleTable see below...   What is in this query to throw a flag?

let
Source = SubHeader,
#"Removed Columns" = Table.RemoveColumns(Source,{"IS Header", "GM Header", "CM Header", "RequiredCenter"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([BS1 Header] = "INVENTORY")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"BS1 Header", "InvtyHeader"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"SubHeader ID", Order.Ascending}})
in
#"Sorted Rows"

Again, the desktop refreshes are working fine.  Only at the service level.    I am using SharePoint.Contents connector - in my initial staging query.

= SharePoint.Contents("https://jorgensenlandandcattle-my.sharepoint.com/personal/xxxx_jorgensenfarms_com")

Are there OneDrive/web connectors that will get to the same place as the above SharePoint.Contents connector?

Any thoughts and ideas are appreciated!  And thank you in advance...

[V-lianl-msft]

Hi @Dellis81 ,

Are there multiple data types in a column?

For connecting to SharePoint using web connector, you can refer to this:https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dellis81]

Hello - no, I don't believe so.  The data model refreshes properly in PBI desktop.

But, after more digging - I think the problem is with SharePoint authentication.   I heve found several references to the authentication loses validity within a hour of refresh initiation.   And that hour is about how long hte process goes before I get the error.    I am now going back to my "old way" of connecting to a local drive, and running refreshes thru a GateWay.   I was really hoping to avoid the gateway process.   OH, the steep learning curve in this process!

thanks for your suggestions.