Contents
If you have already started using the tools of the free Power Query add-in in Microsoft Excel, then very soon you will encounter one highly specialized, but very frequent and annoying problem associated with constantly breaking links to source data. The essence of the problem is that if in your query you refer to external files or folders, then Power Query hardcodes the absolute path to them in the query text. Everything works fine on your computer, but if you decide to send a file with a request to your colleagues, then they will be disappointed, because. they have a different path to the source data on their computer, and our query will not work.
What to do in such a situation? Let’s look at this case in more detail with the following example.
Formulation of the problem
Suppose we have in the folder E:Sales reports lies the file Top 100 products.xls, which is an upload from our corporate database or ERP system (1C, SAP, etc.) This file contains information about the most popular commodity items and looks like this inside:
It’s probably clear right off the bat that it’s almost impossible to work with it in Excel in this form: empty rows through one with data, merged cells, extra columns, a multi-level header, etc. will interfere.
Therefore, next to this file in the same folder, we create another new file Handler.xlsx, in which we will create a Power Query query that will load ugly data from the source upload file Top 100 products.xls, and put them in order:
Making a request to an external file
Opening the file Handler.xlsx, select on the tab Data Command Get Data – From File – From Excel Workbook (Data — Get Data — From file — From Excel), then specify the location of the source file and the sheet we need. The selected data will be loaded into the Power Query editor:
Let’s bring them back to normal:
- Delete empty lines with Home — Delete lines — Delete empty lines (Home — Remove Rows — Remove Empty Rows).
- Delete unnecessary top 4 lines through Home — Delete Rows — Delete Top Rows (Home — Remove Rows — Remove Top Rows).
- Raise the first row to the table header with the button Use first line as headers tab Home (Home — Use first row as header).
- Separate the five-digit article from the product name in the second column using the command split column tab Transformation (Transform — Split Column).
- Delete unnecessary columns and rename the headings of the remaining ones for better visibility.
As a result, we should get the following, much more pleasant picture:
It remains to upload this ennobled table back to the sheet in our file Handler.xlsx the team close and download (Home — Close&Load) tab Home:
Finding the path to a file in a request
Now let’s see how our query looks “under the hood”, in the internal language built into Power Query with the concise name “M”. To do this, go back to our query by double clicking on it in the right pane Requests and connections and on the tab Review choose Advanced Editor (View — Advanced Editor):
In the window that opens, the second line immediately reveals a hard-coded path to our original upload file. If we can replace this text string with a parameter, variable, or a link to an Excel sheet cell where this path is pre-written, then we can easily change it later.
Add a smart table with a file path
Let’s close Power Query for now and return to our file Handler.xlsx. Let’s add a new empty sheet and make a small “smart” table on it, in the only cell of which the full path to our source data file will be written:
To create a smart table from a regular range, you can use the keyboard shortcut Ctrl+T or button Format as a table tab Home (Home — Format as Table). The column heading (cell A1) can be absolutely anything. Also note that for clarity I have given the table a name Parameters tab Constructor (Design).
Copying a path from Explorer or even entering it manually is, of course, not particularly difficult, but it is best to minimize the human factor and determine the path, if possible, automatically. This can be implemented using the standard Excel worksheet function CELL (CELL), which can give out a bunch of useful information about the cell specified as an argument – including the path to the current file:
If we assume that the source data file always lies in the same folder as our Processor, then the path we need can be formed by the following formula:
=LEFT(CELL(“filename”);FIND(“[“;CELL(“filename”))-1)&”Top 100 products.xls”
or in English version:
=LEFT(CELL(«filename»);FIND(«[«;CELL(«filename»))-1)&»Топ-100 товаров.xls»
… where is the function LEVSIMV (LEFT) takes a piece of text from the full link up to the opening square bracket (i.e. the path to the current folder), and then the name and extension of our source data file is glued to it.
Parameterize the path in the query
The last and most important touch remains – to write the path to the source file in the request Top 100 products.xls, referring to cell A2 of our created “smart” table Parameters.
To do this, let’s go back to the Power Query query and open it again Advanced Editor tab Review (View — Advanced Editor). Instead of a text string-path in quotes “E:Sales reportsTop 100 products.xlsx” Let’s introduce the following structure:
Excel.CurrentWorkbook(){[Name=”Settings”]}[Content]0 {}[Path to source data]
Let’s see what it consists of:
- Excel.CurrentWorkbook() is a function of the M language for accessing the contents of the current file
- {[Name=”Settings”]}[Content] – this is a refinement parameter to the previous function, indicating that we want to get the contents of the “smart” table Parameters
- [Path to source data] is the name of the column in the table Parametersto which we refer
- 0 {} is the row number in the table Parametersfrom which we want to take data. The cap does not count and the numbering starts from zero, not from one.
That’s all, in fact.
It remains to click on Finish and check how our request works. Now, when sending the entire folder with both files inside to another PC, the request will remain operational and determine the path to the data automatically.
- What is Power Query and why is it needed when working in Microsoft Excel
- How to import a floating text snippet into Power Query
- Redesigning a XNUMXD Crosstab to a Flat Table with Power Query