Skip to main content

Notifications

Power Apps - Power Query
Answered

Excel Power Query - connect to file on SharePoint - error cannot convert the value "[Binary]" to type Binary

(0) ShareShare
ReportReport
Posted on by 9

Reposting here at suggestion of moderator on MS365 community.

 

I have an Excel file that uses Power Query to connect to another Excel file on SharePoint. A new SharePoint file is posted weekly. The internal structure is the same, but the file name and location change.

 

I'm able to use Power Query parameters for the file name and location. But I'd like to be able to update the file name and location in my spreadsheet, instead of editing the Power Query parameters. However, when I try to do it that way, the connection to the SharePoint file fails with message " Expression.Error: We cannot convert the value "[Binary]" to type Binary. "

 

To get my input values from the spreadsheet, I followed the steps at this page for "Use a cell value to filter data":

 

Full details below. Grateful for any insights you can offer.

 

Code that works

//query 1 create parameter MyFileName for file name

"MyTest.xlsx" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

 

//query 2 create parameter MyFileLocation for file location

"https://mycorp.sharepoint.com/sites/DeptABC/Div123/Service Requests/20240620/" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

 

//query 3 SharePoint connection

let

Source = SharePoint.Files("https://mycorp.sharepoint.com/sites/DeptABC/Div123", [ApiVersion = 15]),

#"GetMyFile" = Source{[Name=MyFileName,#"Folder Path"=MyFileLocation]}[Content],

#"Imported Excel Workbook" = Excel.Workbook(#"GetMyFile")

in

#"Imported Excel Workbook"

 

 

Tables and code that do not work

Spreadsheet table FlNmTbl:

FlNm

MyTest.xlsx

 

Spreadsheet table FlLocTbl:

FlLoc

https://mycorp.sharepoint.com/sites/DeptABC/Div123/Service Requests/20240620/

 

//query 1 named FileNameFromTable gets value for file name

let

Source = Excel.CurrentWorkbook(){[Name="FlNmTbl"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"FlNm", type text}}),

FlNm = #"Changed Type"{0}[FlNm]

in

FlNm

 

//query 2 named FileLocationFromTable gets value for file location

let

Source = Excel.CurrentWorkbook(){[Name="FlLocTbl"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"FlLoc", type text}}),

FlLoc = #"Changed Type"{0}[FlLoc]

in

FlLoc

 

//query 3 SharePoint connection

let

Source = SharePoint.Files("https://mycorp.sharepoint.com/sites/DeptABC/Div123", [ApiVersion = 15]),

//When I start typing FileNameFromTable in the next line, Intellisense recognizes it

//But when I start typing FileLocationFromTable, Intellisense does not recognize it, I have to type the full name

//So I think for some reason my FileLocationFromTable is not working here

#"GetMyFile" = Source{[Name=FileNameFromTable,#"Folder Path"=FileLocationFromTable]}[Content],

#"Imported Excel Workbook" = Excel.Workbook(#"GetMyFile")

in

#"Imported Excel Workbook"

Categories:
  • Verified answer
    ExcelUserRJP Profile Picture
    ExcelUserRJP 9 on at
    Excel Power Query - connect to file on SharePoint - error cannot convert the value "[Binary]" to type Binary
    I was able to make this work by putting my "parameters" in an Excel range instead of a table. I mostly followed the steps at this page Change the Power Query source based on a cell value.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard

Featured topics