I am trying to make a query source relative so that the workbook can be shared across users in our organization. I’ve followed several other tutorials and forum posts but am getting an error that I haven’t seen mentioned yet. The error being:
Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=Type
This error appears at the Source in the applied steps.
I added the formula =LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1) into cell A1 of a new tab in the workbook and then named that cell FilePath. Then, using the Advanced Editor, added 2 lines before the Source step. Just to be specific and clarify for this particular issue, GRNP is my table name and GRNP Trial Balance-BEFORE.xlsx is the filename of the workbook where the query resides.
let
FilePath = Excel.CurrentWorkbook(){[Name="GRNP"]}[Content]{0}[Column1],
FullPathToFile1 = FilePath & "GRNP Trial Balance-BEFORE.xlsx",
Source = Excel.Workbook(File.Contents(FullPathToFile1), null, true),
#"Trimmed Text" = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Trimmed Text",null,"blank space",Replacer.ReplaceValue,{"Column1"}),
I think the error is referring to the …..,null, true) in the Source step but I don’t know what how to fix it.
I am using Excel 2016 with Windows 10 Enterprise. I’m still very much a beginner and the task I’m trying to achieve is still a little too far advanced for me troubleshoot on my own so I would appreciate whatever input you may have!
Bridgett
Try these steps:
- In the spreadsheet, select the cell you named FilePath
- Go to Data and click "From Table/Range"
- On Power Query, click on Close and Load to
- Select "Only Create Connection" and click OK
Try refreshing again and see if it works.
