Column value based on other row and column

[TK12345]

Hi guys,

I have a question about a calculation I need to do in the power query. See data for the expected outcome. I do have 5 columns and I need to make the column . 
Expected outcome enddate previous type

The data is all sorted how it needs to be. 

The first row of each new Cat needs to be empty. Then the second row is looking to the end date of the previous row (or more previous rows) but only if the type is the same as previous row. The 5th will not show an expected outcome date cause there is a new Type. 

The last important thing is the example on row 14. It shows 27-12-2020 because that type already had an endate 2 rows before with the same Cat. 

It is possible to use the id for a key or something. Or duplicate the table with an index merge after.... i tried it but did not turn out great. 

Cat	Enddate	Type	Expected outcome enddate previous type	StartDate	id
1		Type 0		26-11-2019	137175
1	31-3-2022	Type 1		20-6-2020	153676
1		Type 1	31-3-2022	20-6-2020	153677
1	31-3-2022	Type 1		2-12-2020	165553
1		Type 2		1-4-2022	227028
1		Type 2		1-4-2022	227029
2	16-12-2027	Type 3		17-12-2022	241229
3	29-3-2020	Type 3		14-1-2019	114249
3	31-12-2020	Type 3	29-3-2020	30-3-2020	142010
3	1-5-2022	Type 3	31-12-2020	1-1-2021	164519
3	23-3-2025	Type 3	1-5-2022	2-5-2022	230784
4	27-12-2020	Type 4		1-1-2019	113336
4	26-12-2021	Type 5		2-1-2019	113337
4	27-12-2022	Type 4	27-12-2020	28-12-2020	167168
4	31-12-2021	Type 6		1-1-2021	176085
4	26-12-2026	Type 5	26-12-2021	27-12-2021	215129

Could someone help me out?

[v-easonf-msft · Accepted solution]

Hi, @TK12345 

Please check my attached pbix for more details.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZK9jsQgDIRfJUq9SIyNMXmP61ZbXr/tvv0B4ccczRaRsMw3MzZ5Pk+cj/PI38/n/Xv4VlB0gCOPKxdghcr5etyXGY5zi6hDXYG8i6VRRCAcNQ7msHetwMboFz4ONJgoIrz7UCvgwrAi9ZS+v3rVq1RNmqN2hjujrVOpAGpU6dN15/cbFNzcLQKFyeSZ52wDskrs5zmzHn7AcLLsaxNE9SXUkYLAZOVbVSxr9Mgc2WsKlQyl1P+Jw9inmZKZ42T6PtEZmU+7MLr7kPVZ3CktP4YipoGPPQzLuMS8l6LRJ9ljRhtzCT/8awFBef/XHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Enddate = _t, Type = _t, #"Expected outcome enddate previous type" = _t, StartDate = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat", Int64.Type}, {"Enddate", type text}, {"Type", type text}, {"Expected outcome enddate previous type", type text}, {"StartDate", type text}, {"id", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Enddate", type date}, {"Expected outcome enddate previous type", type date}, {"StartDate", type date}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Cat", Order.Ascending}, {"Type", Order.Ascending}, {"StartDate", Order.Ascending}, {"id", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Cat", "Type"}, {{"Data", each _, type table [Cat=nullable number, Enddate=nullable date, Type=nullable text, Expected outcome enddate previous type=nullable date, StartDate=nullable date, id=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Sub_index",0,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Cat", "Enddate", "Type", "Expected outcome enddate previous type", "StartDate", "id", "Index", "Sub_index"}, {"Cat", "Enddate", "Type", "Expected outcome enddate previous type", "StartDate", "id", "Index", "Sub_index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Result", each if [Sub_index] = 0 then null else  try #"Expanded Custom"[Enddate]{ [Index]-1 } otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Sub_index"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi, @TK12345 

In PQ, you can try to group by "Cat" and "Type" and create a sort column. Then get the value of the previous row based on the sorted column.

Add index to nested tables in Power Query 

Get Value from Previous Row using Power Query 

Best Regards,
Community Support Team _ Eason