Power Query: Binary Function

Welcome to our Power Query blog. This week, I look at using a function when processing binary data.

I have some data for my salespeople. The data is in the form of a collection of Excel files held in one folder. I would like to create a custom function that I can run against this data before I combine the files into one query.

I start in the ‘Get & Transform’ section of the Data tab, where I choose to create a ‘New Query’. From the dropdown, I can select ‘From File’ and then ‘From Folder’.

I select the folders location and view the files. I choose to ‘Transform Data’.

I right-click on the first value in the Content column.

I am going to ‘Add as New Query’.

This has created a new query which points at my first Excel file, that just happens to be Derek’s. I rename my query Sample_Expenses.

I create a new parameter from the Home tab.

I call my new parameter Expense_Parameter, and once I choose type Binary. It finds the Sample_Expenses query, and allows me to select it as the default and current value.

Once I have created Expense_Parameter, I can right-click it in the Query pane and create a Reference query.

I call my new query Transform_Sample_Expenses.

I can right-click on my new query and choose ‘Create Function’.

This function will be linked to my current query, Transform_Sample_Expenses. I call this function Transform_Expenses.

My function query expects a binary file as a parameter, and defaults to Derek’s expenses. I can now return to my Transform_Sample_Expenses and make some transformations.

I need to treat my file as an Excel file, so I right-click on the file and choose Excel.

Since this is the format of all the files in my folder, I continue to transform my data.

I have removed the first row, expanded the table data, and removed the columns I don’t need. Finally, I fill down on the name. I ‘Close & Load’ from the File tab to save my query.

Back in the query for my Expense folder (‘Expenses Folder Custom’), I can add a column which will call the function I have created.

I can select the Transform_Expenses function.

I am then prompted to enter the binary parameter, and I choose the Content column.

Table data appears in my new column, and if I select one of the lower table values, I can see that I have transformed all of my Excel files, ready to be combined.

I can expand the table data and remove the columns I don’t need.

Come back next time for more ways to use Power Query!