Choose second row per group

[radu92]

I have the following data: 

Case no.	Activity type	Date
1	New	01-01-2016
1	update	02-01-2016
1	update	03-01-2016
1	update	03-01-2016
2	new	05-01-2016
2	update	05-01-2016
2	update	07-01-2016

I would like to create two new columns:

1.) FirstUpdateDate column should have the date of the first update for the specific case 

2.) SecondUpdateDate collumn should have the date of the second update, if available, otherwise null

Thanks in advance! 

[ImkeF · Accepted solution]

This is one option:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLLQeSBoZ6QGRkYGimFKsDkSgtSEksSQXJGeGRMyZazggolAexzBRDAqEJn5w5ipwxwkATDAmsBsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case no. " = _t, #"Activity type" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case no. ", Int64.Type}, {"Activity type", type text}, {"Date", type date}}),
    FilterOnlyUpdate = Table.SelectRows(#"Changed Type", each ([Activity type] = "update")),
    GroupOnCaseNo = Table.Group(FilterOnlyUpdate, {"Case no. "}, {{"Partition", each _[Date], type table}}),
    FirstUpdate = Table.AddColumn(GroupOnCaseNo, "FirstUpdate", each List.First([Partition])),
    SecondUpdate = Table.AddColumn(FirstUpdate, "SecondUpdate", each try List.Range(List.FirstN([Partition],2),1){0} otherwise null),
    MergeWithStepChangedType = Table.NestedJoin(#"Changed Type",{"Case no. "},SecondUpdate,{"Case no. "},"NewColumn",JoinKind.LeftOuter),
    ExpandDesiredColumns = Table.ExpandTableColumn(MergeWithStepChangedType, "NewColumn", {"FirstUpdate", "SecondUpdate"}, {"FirstUpdate", "SecondUpdate"})
in
    ExpandDesiredColumns

You have to replace the Source-step by the reference to your table.

[v-sihou-msft · Accepted solution]

@radu92

In this scenario, to get the second update date, you need to give an index column for dates wihtin each Case Number and Actuvity Type group. Then you can get the first and second update date based on this index column. Please refer to steps below:

1. Create a date value column (integer).

2. Create an index column with RANKX() function.

Rank within Group = RANKX(FILTER(Table6,Table6[Case no. ]=EARLIER(Table6[Case no. ])&&Table6[Activity type]=EARLIER(Table6[Activity type])),Table6[Date Value],,ASC)

3. Create two measures to get the first and second update date.

First Update = CALCULATE(MIN(Table6[Date]),FILTER(Table6,Table6[Rank within Group]=1 && Table6[Activity type]="update"))

Second Update = CALCULATE(MIN(Table6[Date]),FILTER(Table6,Table6[Rank within Group]=2 && Table6[Activity type]="update"))

Regards,

[radu92]

Thank you both for your help! It's exactly what I was looking for! 

[ImkeF · Accepted solution]

This is one option:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLLQeSBoZ6QGRkYGimFKsDkSgtSEksSQXJGeGRMyZazggolAexzBRDAqEJn5w5ipwxwkATDAmsBsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case no. " = _t, #"Activity type" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case no. ", Int64.Type}, {"Activity type", type text}, {"Date", type date}}),
    FilterOnlyUpdate = Table.SelectRows(#"Changed Type", each ([Activity type] = "update")),
    GroupOnCaseNo = Table.Group(FilterOnlyUpdate, {"Case no. "}, {{"Partition", each _[Date], type table}}),
    FirstUpdate = Table.AddColumn(GroupOnCaseNo, "FirstUpdate", each List.First([Partition])),
    SecondUpdate = Table.AddColumn(FirstUpdate, "SecondUpdate", each try List.Range(List.FirstN([Partition],2),1){0} otherwise null),
    MergeWithStepChangedType = Table.NestedJoin(#"Changed Type",{"Case no. "},SecondUpdate,{"Case no. "},"NewColumn",JoinKind.LeftOuter),
    ExpandDesiredColumns = Table.ExpandTableColumn(MergeWithStepChangedType, "NewColumn", {"FirstUpdate", "SecondUpdate"}, {"FirstUpdate", "SecondUpdate"})
in
    ExpandDesiredColumns

You have to replace the Source-step by the reference to your table.