Power Query: Binary Function
20 January 2021
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!