SOLVED

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

Copper 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.

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.

 

Bridgett5_0-1658496639351.png

 

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

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 VI_Migration (Silver Contributor)
Solution

@Bridgett_B 

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.

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Bridgett_B 

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.

View solution in original post