Vallue based on previous rows and other column

[TK12345]

Hi guys,

** NEEDS TO BE IN POWER QUERY** No calculated column

I need help with the following:

I need to create the column: expected outcome enddate

This column needs to be based on the column cat. The expected outcome of the first row of each Cat needs to be empty. As well as the first row of each new type. The expected outcome enddate previous type needs to be filled if the previous row has the same  cat and the same Type. One addition to that, it is possible that within 1 cat, the type changes but has been ended on a previous row above. So for example row 14 gives uit 27-12-2020, because the cat is still 4 and the type 4 has an enddate two rows above. 

I have tried with duplicate table add index and merge back. Did not work out great. 

Who could help me, you could also use startdate and id maybe. 

[Anonymous · Accepted solution]

Hi @TK12345 ,

You may refer to this M code to learn more details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFLbgMhEETvMmtLTfUX3yM7y0vvs83tQxOYEGIllkYa0bwqiuJ2O3Bcjva9fbw/SvtzwZUAYm8LSCDsuF++MC7MJCQY/JgVcuLUwsTDT/wvKv43BRMn7mYmuylPpVIXcBSur0DXDo2dyEPgA5eJ5zByoeAhkJlKqE1WPusiaCaFsm54szpvtnhIL0Kbuqw8k9FGI917DWr4YW6ZRfbs1ktjKVG107r2GQPXJXpPLiK+4uj4bMa+cZ54rDg/cR9H1h4+4PWX/9mMb3cNL9VW3J/EGRn7Q8GQD3X/BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Enddate = _t, Type = _t, StartDate = _t, id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat", Int64.Type}, {"Enddate", type date}, {"Type", type text}, {"StartDate", type date}, {"id", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cat", "Type"}, {{"Rows", each _, type table [Cat=nullable number, Enddate=nullable date, Type=nullable text, StartDate=nullable date, id=nullable number]}}),
    #"Added Custom1" = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom1", "Rows", {"Cat", "Enddate", "Type", "StartDate", "id", "GroupIndex"}, {"Rows.Cat", "Rows.Enddate", "Rows.Type", "Rows.StartDate", "Rows.id", "Rows.GroupIndex"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Rows",{"Cat", "Type"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Rows.Cat", "Cat"}, {"Rows.Enddate", "Enddate"}, {"Rows.Type", "Type"}, {"Rows.StartDate", "StartDate"}, {"Rows.id", "id"}, {"Rows.GroupIndex", "GroupIndex"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each let 
_Cat = [Cat] ,
_Type = [Type],
_Index = [GroupIndex]
in 
Table.SelectRows(#"Renamed Columns",
each [Cat] = _Cat 
and 
_Type = [Type]
and
_Index-1 = [GroupIndex])[Enddate]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Orginal Table:

New Table:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.