Power Query: New Year Function
6 January 2021
Welcome to our Power Query blog. This week, I look at an example using custom functions.
I have some supplier data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I intend to find out the percentage of the total sales of each tent per customer, and show it alongside the transactions. I start by extracting my data to Power Query using ‘From Table’ in the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I accept the defaults.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I will need two copies of this query, so I can right-click on my query in the Queries pane and choose Duplicate.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I can now rename my queries. One is going to be my ‘Supplier Rentals’ query and one is going to be a function which I will call fnSupplierTotal.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
In fnSupplierTotals, I delete the ‘Changed Type’ step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
In the Transform tab, I choose ‘Group By’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I filter on Supplier and choose just ‘Tents R Us’ (any supplier would do; I just need the syntax).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I can now apply this to get my query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
In the Advanced Editor, which I accessed from the Home tab, I can see the current M code:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
The M code is:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Supplier"}, {{"Supplier Total", each List.Sum([Rentals]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Supplier] = "Tents R Us"))
in
#"Filtered Rows"
Since this is now going to be a function, I start with a line defining my parameters.
(sourceTable as table, supplierName as text) =>
Since I am passing in the source, I don’t need the source line, so I can delete it.
That means the #"Grouped Rows" step that referred to ‘Source’ must now use sourceTable instead. I also need to change ‘Tents R Us’ to my supplier parameter.
I can also tweak the code to return a value instead of a table with the value in by adding a step:
#"SupplierTotal" = Record.Field(#"Filtered Rows"{0},"Supplier Total")
(Record.Field is a Power Query function which returns single value from a column.)
My M code is now:
(sourceTable as table, supplierName as text)=>
let
#"Grouped Rows" = Table.Group(sourceTable, {"Supplier"}, {{"Supplier Total", each List.Sum([Rentals]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Supplier] = supplierName)),
#"SupplierTotal" = Record.Field(#"Filtered Rows"{0},"Supplier Total")
in
#"SupplierTotal"
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image12.png/a1537847463e660a31158c8032525438.jpg)
My function is ready to use. I can test it by pointing at my ‘Supplier Rentals’ query and choosing ‘Tents R Us’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image13.png/917da985be13220165c8d2823e95344f.jpg)
This should give me the total for ‘Tents R Us’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
So far, so good.
Now, I go back to the ‘Supplier Rentals’ Query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I am adding a custom column from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image16.png/d082e3477129350b8a2a589156028e63.jpg)
The M code I have used is:
= [Rentals]/fnSupplierTotal(Source, [Supplier])
I am dividing the Rentals value by the total for the Supplier, as provided by my function.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
To show this as a percentage I can just change the Data Type to percentage from the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/214/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
Come back next time for more ways to use Power Query!