Please note javascript is required for full website functionality.
MVP

Blog

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:

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.

I accept the defaults.

I will need two copies of this query, so I can right-click on my query in the Queries pane and choose Duplicate.

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.

In fnSupplierTotals, I delete the ‘Changed Type’ step.

In the Transform tab, I choose ‘Group By’.

 I filter on Supplier and choose just ‘Tents R Us’ (any supplier would do; I just need the syntax).

 I can now apply this to get my query.

In the Advanced Editor, which I accessed from the Home tab, I can see the current M code:

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"

My function is ready to use.  I can test it by pointing at my ‘Supplier Rentals’ query and choosing ‘Tents R Us’.

This should give me the total for ‘Tents R Us’.

So far, so good. 

Now, I go back to the ‘Supplier Rentals’ Query.

I am adding a custom column from the ‘Add Column’ tab.

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.

To show this as a percentage I can just change the Data Type to percentage from the Transform tab.

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

Newsletter