Yarado ExcelRunPowerQuery function combine the power of both

Yarado ExcelRunPowerQuery function combine the power of both

Power Query is a data transformation and data preparation engine which comes with Excel. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations

In combination with the Yarado function ExcelRunPowerQuery, is become a real powertool to getdata, transform data and process data throughout a Yarado tasks. Completely unattended. For an unattended approach I us a set up as described below:

The Function requires an excel file and an output csv file:
image

To get out the max op this tool is to create an excel file that :

Create an excelfile containing the powerquery code and a link to the “source data”. With this we achieve that every time the function is run, the latest source data is retrieved, edited and saved as a .csv file.
image

NOTE running the function can take some time, be sure to wait untill the function is ready, for stability I add a stap after the function with a delay.

The Source.xlsx is a data file or files within a folder with the same format. Normally created in steps within a task for example to download certain set of data

Creating the ExcelfileWithPowerQuery.xlxs , where only a link to the data source is established. Within Excel use

Select the from workbook or from folder (containing multiple workbooks). Go to the Data tab and select the Get Data command in the Get & Transform Data section. Then go to From File and choose From Workbook. This will open a file picker menu where you can navigate to the file you want to import. Select the file and press the Import button.
image

The Query editor wil open.For detailed information about PowerQuery the is a lot on the internet for example https://www.howtoexcel.org/power-query/the-complete-guide-to-power-query/
The look and feel of PowerQuery is as in the figure below.
image

Within powerquery it is possible to transform you data. remove columns, combine files, format data, etc… Every time Yarado opens this file it will process these steps. Finally Yarado will save this processed file to a output file, in this example output.csv. Within the advanced editor within PowerQuery you can also use the advanced editor, code wil then look something like:
image

EXAMPLE {including files}:

This example explains a basic transformation of a source file with Yarado, excel and powerquery. The files that are used within this example:

Inputfiles:

Source.xlsx

ExcelfileWithPowerQuery.xlxs

Yarado task:
PowerQueryFunction.cfg

PowerQueryFunction.tsk
image

For this example create a map C:\Temp\PowerQuery\Data where you place the file:

Source.xlsx contains:
image

Place the file ExcelfileWithPowerQuery.xlxs in de folder C:\Temp\PowerQuery, this import because there is al link within the file to the folder were the source.xlsx file is.

With ExcelfileWithPowerQuery.xlxs we wil remove the colums Numbers, ID
image

Yarado will create a *.csv file were the columns are deleted as configure in PowerQuery in this example. If the source data is changed, the same colums will be deleted. In this example it are just simple operations, but due to possible much more complex transformations, this is a stable solution.

ExcelFileWithPowerQuery.xlsx (19.2 KB) PowerQueryFunction.cfg (237 Bytes) PowerQueryFunction.tsk (1.1 KB) Source.xlsx (10.4 KB)

4 Likes