Column value based on other row and column

Question

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 .&nbsp;Expected outcome enddate previous typeThe data is all sorted how it needs to be.&nbsp;The first row of each new Cat&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;CatEnddateTypeExpected outcome enddate previous typeStartDateid1&nbsp;Type 0&nbsp;26-11-2019137175131-3-2022Type 1&nbsp;20-6-20201536761&nbsp;Type 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31-3-202220-6-2020153677131-3-2022Type 1&nbsp;2-12-20201655531&nbsp;Type 2&nbsp;1-4-20222270281&nbsp;Type 2&nbsp;1-4-2022227029216-12-2027Type 3&nbsp;17-12-2022241229329-3-2020Type 3&nbsp;14-1-2019114249331-12-2020Type 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;29-3-202030-3-202014201031-5-2022Type 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;31-12-20201-1-2021164519323-3-2025Type 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1-5-20222-5-2022230784427-12-2020Type 4&nbsp;1-1-2019113336426-12-2021Type 5&nbsp;2-1-2019113337427-12-2022Type 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 27-12-202028-12-2020167168431-12-2021Type 6&nbsp;1-1-2021176085426-12-2026Type 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;26-12-202127-12-2021215129Could someone help me out?

v-easonf-msft · Accepted Answer

Hi,&nbsp;TK12345&nbsp;
Please check my attached pbix for more details.
M code:
&nbsp;
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"
&nbsp;

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

Forum Discussion

Column value based on other row and column

2 Replies

Related content

Splitting Column values

Matching Column Values

Check column for similar values

New Column

Grouping rows by Column and then updating Column

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