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

[Anonymous]

I saw this same issue in a post here from mid-2017. There was no solution then, but a work-around. I'm wondering if there is a solution yet.

As a data source I am combining many .xlsx files in one sharepoint folder using the "get data/sharepoint folder" tool, then merging that query with another query to create a new query that generates the table I need. This all is working fine in PBI Desktop, including all refreshes. It publishes okay also to the PBI Service. However, refreshes fail and I get the message in the subject line. Is there a fix for this? The work around suggests building the table in Power Query and then using that table as the data source. Is that still the only way to deal with this issue?

- Rockmond

[v-shex-msft]

HI @Anonymous,

Is there any advanced query operations in your query tables? Can you please provide more detail information?

AFAIK, custom functions and some of advanced operations only works on power bi desktop site. They will caused some issues when you used on service side.

Regards,

Xiaoxin Sheng

[Anonymous]

Here is the M code for the query causing problems:

let
Source = SharePoint.Files("https://wagonwheeltitle-my.sharepoint.com/personal/craig_wagonwheeltitle_com/", [ApiVersion = 15]),
#"Filtered Rows1" = Table.SelectRows(Source, each ([Folder Path] = "https://wagonwheeltitle-my.sharepoint.com/personal/craig_wagonwheeltitle_com/Documents/RPC Data/")),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows1", "Transform File from Query1", each #"Transform File from Query1"([Content])),
#"Filtered Rows" = Table.SelectRows(#"Invoke Custom Function1", each true),
#"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Query1"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query1", Table.ColumnNames(#"Transform File from Query1"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Closing Date", type date}, {"Column2", type any}, {"Client Name", type text}, {"Column4", type any}, {"Property Address", type text}, {"Column6", type any}, {"City", type any}, {"Column8", type any}, {"ST", type any}, {"Column10", type any}, {"Zip Code", type any}, {"Column12", type any}, {"Referral Source", type text}, {"Column14", type any}, {"Revenue", type number}, {"Column16", type any}, {"Closing Type", type text}, {"Column18", type any}, {"Check #", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column4", "Column6", "Column8", "Column10", "Column12", "Column14", "Column16", "Column18", "Check #", "City", "ST", "Zip Code", "Property Address"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Closing Type", Text.Trim, type text}, {"Referral Source", Text.Trim, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text",{"Referral Source"},#"Referral Master Table",{"Trimmed Source"},"Referral Table",JoinKind.LeftOuter),
#"Expanded Referral Table" = Table.ExpandTableColumn(#"Merged Queries", "Referral Table", {"Referrer1", "Referrer2"}, {"Referrer1", "Referrer2"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Referral Table",{"Client Name", "Referral Source"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Source.Name", "Closing Date", "Revenue", "Closing Type"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value", "Referrer"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Source.Name"})
in
#"Removed Columns2"

[v-shex-msft]

HI @Anonymous,

I think this issue should more relate to your custom function, current custom function only works on desktop side.

Regards,

Xiaoxin Sheng