In this post of my blog
http://zsvipullo.blogspot.it/2018/05/create-simple-excel-power-Query-user.html
I described how create a simple Power Query User Function.
Now I try to show an example of how this function can be used in a real case.
I suppose to have a SQL database where I can only access to it in read mode way and I can’t ask to SQL developer to add new business logic database side.
The database contains the activity performed by some technicians inside 3 SQL tables:
- User
- Cost
- TimeSheet
The goal of my Power Query project is to calculate the total cost for each user activity.
I suppose to have linked those 3 table to my Power Query project yet.
The User table contains the master data of my technician
The Cost table contains the cost for each user in a specific time frame: between 2018-01-01 and 2018-12-31 the user with id 5 costs 82 euros.
The TimeSheet Table contains the User id, the date of the task and the hours spent to close the task.
Then… my Power Query application must to:
- Join between User and TimeSheet table by using the Id Field
- Calculate the total cost of the task (cost in a specific date * hours number) for each timesheet Row.
The first operation is very simple and you can perform it by using the Power Query Merge Query command
https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9
The second operation is not possible by using the OOB Power Query Function.
Then I decided to create a Custom Function which receives as input
- UserId
- TimeSheetDate
- HoursNumber
And returns the user cost for a specific day * the hours number.
This value must be shown as a calculate column near the merged query talked about above.
Let’s create the Custom Function by creating a New Blank Query and editing it with Advanced Editor.
Define the function parameter and its return value.
(userId as number, tsDate as date, hours as number) as number
=>
let
Source = ""
in
Source
Now I’have to add the let statements in order to calculate the cost value.
First I’ve to filter the Cost query by User ID
(userId as number, tsDate as date, hours as number) as number
=>
let
#"Filtered By User Rows" = Table.SelectRows(#"Cost", each [UserId] = userId)
in
#"Filtered By User Rows"
Then I filter by StartDate Field
(userId as number, tsDate as date, hours as number) as number
=>
let
#"Filtered By User Rows" = Table.SelectRows(#"Cost", each [UserId] = userId),
#"Filtered By Start Date Rows" = Table.SelectRows(#"Filtered By User Rows", each [StartDate]
in
#"Filtered By Start Date Rows"
And by EndDate Field
(userId as number, tsDate as date, hours as number) as number
=>
let
#"Filtered By User Rows" = Table.SelectRows(#"Cost", each [UserId] = userId),
#"Filtered By Start Date Rows" = Table.SelectRows(#"Filtered By User Rows", each [StartDate]
#"Filtered By End Date Rows" = Table.SelectRows(#"Filtered By Start Date Rows", each [EndDate] >= tsDate )
in
#"Filtered By End Date Rows"
Now, I’ve to convert a table result to a list value and then take the first value of Cost
(userId as number, tsDate as date, hours as number) as number
=>
let
#"Filtered By User Rows" = Table.SelectRows(#"Cost", each [UserId] = userId),
#"Filtered By Start Date Rows" = Table.SelectRows(#"Filtered By User Rows", each [StartDate]
#"Filtered By End Date Rows" = Table.SelectRows(#"Filtered By Start Date Rows", each [EndDate] >= tsDate ),
#"ValueList" = Table.Column(#"Filtered By End Date Rows", "Cost"),
#"CostResult" = List.First(#"ValueList") * hours
in
#"CostResult"
At this point I can rename my query as GetTSHours and try it by using the Power Query User interface.
If all is ok, you can use your function as a column of TimeSheet query by using the command Invoke Custom Function from the Add Column ribbon tab and configure it like the picture below, passing the Timesheet field value to the Custom Function
The result is a new cololumn with the total cost value calculated by the custom function.
