Shulou(Shulou.com)12/24 Report--

Hello, everyone. I'm studying the cool duck of PowerQuery.

When we use PowerQuery to get an external Excel file, the absolute path is passed in.

As shown in the following figure, after we import the Excel file and enter the PQ editor, we can see that the external Excel file is an absolute path.

Once the location of the imported file changes, there will be a pop-up error when it is opened and refreshed.

Although click Edit Settings in the Edit error bar, click the Browse button and reselect the specified path to update the path.

However, if there are too many worksheets to import external files, it is very troublesome to change the paths one by one.

The table queried by ▲ needs to change the path header of the file one by one.

Today, Xiao Shuang will take you to expand your ideas and get the workbook path dynamically.

1. Path parameterization since you want to change the path repeatedly, there is generally a way to parameterize the path.

Specific operations:

❶ create a new path parameter

Under the "Home" tab, select "manage parameters" and "New parameters".

① name: path.

② type: text.

Values recommended by ③: values list (if you have more than one common path, you can use the values list type. Only one uses the text type).

④ enter values list information, default value, current value.

⑤ clicks the [OK] button, and the path parameter will be created!

After we have created the parameters, we need to reference the parameters in the data source.

Change ❷ path string to parameter

There are two ways to do this.

One is to change the data source.

Under the Home tab, click data Source Settings

In the data source settings

①, click [change Source].

The ② parameter name is: path.

③ type: parameter.

④ clicks the [OK] button to close.

Another way is to change the M function formula directly.

Change the query to the file path to the parameter name (path), respectively.

We have parameterized the path above. Later, once the location of the external file changes, we only need to change the path parameters, not one by one to change the path of the query.

At this point, the path parameters are settled.

However, the previous change to the parameter value of the path is for the partners who have the basis of PQ, so is there any way for us to choose the specified path and pass the information of the path to the parameters?

To achieve this effect, we need to use VBA.

2. First of all, let's create a new parameter to see how the M function is written.

Since it is up to the user to choose the path, you can choose the text for the type of the parameter.

Next, let's take a look at how the M function corresponding to this parameter is written.

Select the query [path] and click [Advanced Editor].

As you can see from the figure, the parameters correspond to the M language as follows:

"C:\ Users\ 80522\ Desktop\ Little Duck xlsx" meta [IsParameterQuery=true, Type= "Text", IsParameterQueryRequired=true] through observation, we can construct the following form.

Path + "meta [IsParameterQuery=true, Type=" Text ", IsParameterQueryRequired=true]" then the following is to use VBA to perform the operation of new path parameters.

The main ideas are as follows:

Through the FileDialog object, let the user select the specified file, get the path string, construct the M function formula of the path parameter, use VBA to add the PQ query (Add method), and let the code execute the query of the new path parameter with the M function formula in the PQ editor. Delete the path parameter query if it already exists.

Create a new module and enter the VBA code I wrote according to the idea.

Sub Select parameter () Dim dig, path Set dig = Application.FileDialog (msoFileDialogFilePicker) With dig .Filters.Add "Excel file", "* .xls *" 1. InitialFileName = ThisWorkbook.FullName If. Show 0 Then path = dig.SelectedItems (1) On Error Resume Next ThisWorkbook.Queries ("path") .Delete ThisWorkbook.Queries.Add Name:= "path" Formula:= _ "" & path & "&" meta [IsParameterQuery=true, Type= "" Text "" IsParameterQueryRequired=true] "ThisWorkbook.Queries (" path "). Refresh End If End With End Sub last Let's insert a picture and specify the above macro code.

Click [Picture], right-click, select [specify Macro], location [current workbook], [Select parameters], and click the [OK] button.

3. Effect demonstration now, let's do a test. In this case, I have imported the Excel file of the test folder in advance, and the file path references the path parameters (in this case, the data source path exists).

Then, I moved the Little Duck file in the Test folder to the path changed folder. The location of Little Duck's workbook has changed.

At this point, after updating the data source, we can see that the error message shows [file not found].

Next, we click on the picture, perform the macro operation, select the moved file, and make sure.

At this point, you will see that the link in the query does not indicate an error. At the same time, the parameters of the path are also changed to the file path we just selected.

Here, has everyone get arrived yet?

This article may be a little difficult, but it is more practical. Friends who don't fully understand can collect it first.

4. Write at the end because we know that PowerQuery refers to an external file, which is an absolute path, not a relative path.

So we came up with the idea of using the path as a parameter and referencing it into the query, so we had the idea of creating a new parameter.

However, it is still inconvenient. At this point, we began to think: for small partners who will not use PowerQuery to change the path, can they choose their own path through the outside? Hence the idea of VBA code.

Because of the use of VBA, so the file suffix, friends remember to save as xlsm format!

This is the overall idea of this article.

By the way, the version used in this article is the latest version of Office 365. the tips for different versions may be different, so please pay attention.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.