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"