Expression error when changing a source from absolute to relative path in Power Query

Occasional Contributor

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.





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. 



    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!



4 Replies
Can you post some screenshots of the previous steps? Like the one you posted for Source but for FilePath and FullPathToFile1.
best response confirmed by Sergei Baklan (MVP)


Try these steps:


  • In the spreadsheet, select the cell you named FilePath
  • Go to Data and click "From Table/Range"
  • Rsartori76_0-1658501528450.png
  • On Power Query, click on Close and Load to
  • Rsartori76_1-1658501675824.png


  • Select "Only Create Connection" and click OK
  • Rsartori76_2-1658501742639.png

Try refreshing again and see if it works.