Power Query – Source Reference as File Path in Cell

Power query helps in doing automation in an efficient manner. It allows users to utilize files stored in specific locations and apply routine transformation steps on those files. It allows users to embed file paths and file sources in an Excel cell. The end user can make use of named ranges and Excel cells as references to access multiple files. In this article, we will learn how to refer to Source as a file path in a cell. 

Adding Source Reference as File Path in Cell

Let us take a basic example of how to make use of Excel cells in manipulating file paths. Below is an example where 2 Excel files are there, Power query1.xlsx and Power query2.xlsx. 

The reference to source as file path, is only valid if the excel files, have same table name, and same number of columns in it. For example, Power query1.xlsx and Power query2.xlsx, has same table name(Table_src) and same number of columns. 

Step 1: In the above example, Excel cells B2 and B3 contain the path of the Excel file. Let us bring the data to be used for transformation.

The name of cell B2, should be replaced with FileP, as shown in the image below. FileP will be the name range cell of B2. 

Step 2: To do this, go to the Data ribbon, and click on Get data. Select the option From file and select from Excel/workbook.

Step 3: Select the Python editor1.xlsx from your pc. Select the transform data option. 

Step 4: In the query editor, go to the view tab, and select Advanced editor.

Step 5: It shows the code for the Advanced Editor. 

Step 6: Change the Code from the above code to this code. The only difference between the two codes is that we have replaced the path, with the name range of that cell. 

let

    Source = Excel.CurrentWorkbook(){[Name=”FileP”]}[Content],

    #”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“D:\gog\Power query 2.xlsx”, type any}})

in

    #”Changed Type”

Step 7: The Data of Power query1.xlsx is shown below. 

Step 8: Now, cell B2(FileP), can show the data of any Excel file. For example, if the path of cell B2, is changed from Power query1.xlsx to Power query2.xlsx. Then, click on the Refresh All button. 

Step 9: The data of Power query2.xlsx is shown below.