How to Solve with M

Power Query
1

I am wondering if someone could help with M wizardry with this problem?

My problem relates to understanding time taken between assessments made for employees based on the last assessment conducted. I have attached a mock up of two tables, employees and assessments.

I would like to know if it is possible (sure it is, but cannot figure it out!) to take the data and perform the check on each employee and their assessments for the time taken (not worried about the period, days, months or years) between each assessment such that I can check to see if the period between the assessment was less than 1 year. This is a maximum period we would allow between assessments, but there can be more than one assessment conducted within the period from the last one.

I have the employee table with ID, Name and Ref. Date (servers as a starting point) and assessment table showing the date upon an assessment was made.

Data
Data
Test.pbix (21.2 KB)

Hope its clear enough to understand if not please shout out!

2

Hi @David,

See if this works for you.
Just copy the entire M script into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc9BC4JAEAXg/7JnB2Ym17ZjkB0EK+goHkzmVrpEBP57WVRcNnJv7/Ax701Vqby8IZJKVP6yz34QcZkN3MUCI2WqTv4g1HBtPw7tNxAtyGygbKk7rIg9xFPd0b4jiHBGjCtKPeQypVA2Q7DpByGcpI2gHcKl/wabtIf0hIqmCy6FCA2c5RG5xDgj9109Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Name = _t, #"Ass. Date" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"Name", type text}, {"Ass. Date", type date}}),
    CombineData = Table.ExpandTableColumn( Table.Group( ChType, {"Employee ID", "Name"},{{ "nTable", each Table.FromColumns({List.RemoveLastN( List.InsertRange(_[#"Ass. Date"],0,{Table.SelectRows(Employees, each _[Employee ID] = [Employee ID])[Ref Date]{0}}),1), _[#"Ass. Date"]},{"Date", "Ass. Date"}),type table}}),"nTable",{"Date","Ass. Date"}),
    DaysDiff = Table.AddColumn(CombineData, "Total Days", each Number.From( [Ass. Date] - [Date] ), Int64.Type)
in
    DaysDiff

Returns this result.

image
image836×283 8.01 KB

I hope this is helpful.

3

@Melissa super quick response!

OK trying to figure this out but assume its the CombineData row where this happens and where I have no idea on this. Think I understand what this is trying to do, for each employee find the Ref Date and then take each Ass. Date and add in to the mix until the next employee?

I think its not quite correct as the starting date (or Ref date from Employees Table) in the picture is picking 30-Sep-2015 which is for Employee1 and applying to every Employee in the assessment table as can be seen on rows 1 (correct), row 5, row 7 and row 10 which is not correct.

4

Hi @David,

Sorry about that, only had a few minutes and totally missed that.
Give this a go instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc9BC4JAEAXg/7JnB2Ym17ZjkB0EK+goHkzmVrpEBP57WVRcNnJv7/Ax701Vqby8IZJKVP6yz34QcZkN3MUCI2WqTv4g1HBtPw7tNxAtyGygbKk7rIg9xFPd0b4jiHBGjCtKPeQypVA2Q7DpByGcpI2gHcKl/wabtIf0hIqmCy6FCA2c5RG5xDgj9109Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Name = _t, #"Ass. Date" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"Name", type text}, {"Ass. Date", type date}}),
    CombineData = Table.ExpandTableColumn( Table.Group( ChType, {"Employee ID", "Name"},{{ "nTable", each let EmpID = [Employee ID]{0} in Table.FromColumns({List.RemoveLastN( List.InsertRange(_[#"Ass. Date"],0,{ Table.SelectRows(Employees, each [Employee ID] = EmpID )[Ref Date]{0}? }),1), _[#"Ass. Date"]},{"Date", "Ass. Date"}),type table}}),"nTable",{"Date","Ass. Date"}),
    DaysDiff = Table.AddColumn(CombineData, "Total Days", each Number.From( [Ass. Date] - [Date] ), Int64.Type)
in
    DaysDiff

.
Returns this result

image
image832×283 8.66 KB

.
I hope this is helpful

5

@Melissa Perfect just the job!

Many thanks for your assistance on this!