Compering multiple rows

[Sharkybu]

Hi,

I have a table containing info about people positions in the company and every time a change is made it appears as a new row.

The data will look like this:

ID	Unit	Position	Start date	ended
Abc	a	1	1.1.24	Yes
Abc	a	2	2.1.24	Yes
Abc	b	1	3.1.24	No
Xyz	a	1	1.1.24	Yes
Xyz	b	2	2.1.24	No
Efg	b	1	1.1.24	Yes
Efg	a	1	2.1.24	Yes
Efg	a	2	3.1.22	No
Lmn	a	1	2.1.24	Yes
Lmn	a	2	3.1.24	Yes
Lmn	a	3	4.1.24	Yes

Person ABC was promoted and then transferred units. (given the wrong position)

Person XYZ moved units and promoted at the same time.

Person EFG moved units and then promoted (was given a wrong promotion date)

Person LMN retaired

My end result should be a new column or new table that shows each person's last position change or if there is an error.

ID	changes	Mistakes in data
Abc	Changed units	Position error
Xyz	Promoted and changed units
Efg	Promoted	Dates error
Lmn	Retired

I tried comparing each row to the previous one but in must cases I get multiple results.

ID	changes	Mistakes in data
Abc	Promoted
Abc	Changed units	Position error
Xyz	Promoted and changed units
Efg	Changed units
Efg	Promoted	Dates error
Lmn	Promoted
Lmn	Promoted
Lmn	Retired

And then when I filter by change, I get people I don’t want to get.

How can I get only the final change?

Thank you so much 

[v-xinruzhu-msft · Accepted solution]

Hi @Sharkybu 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRSgRiQxDWN9Q3MjAyATIjU4uVYnWQFRiBMC4FSVATjBEK/PLB8hGRUfhtgChIwrQBaoCrmzuSBVgMgCiA2YDFiQgFRggnGiFs8PH1w28AQoERqh8xFRgDsQmmgrLilCT8wYCkAkdIQ1UkY5oB8kcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Unit = _t, Position = _t, #"Start Date" = _t, ended = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Unit", type text}, {"Position", Int64.Type}, {"Start Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Unit", "Position", "Start Date", "ended", "Index"}, {"Unit", "Position", "Start Date", "ended", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Unit_change", each let 
_filter1=Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID]),
_filter2=Table.SelectRows(_filter1,(x)=>x[Index]=List.Max(_filter1[Index])-1),
_unit=List.Max(_filter2[Unit])
in if List.Max(_filter1[Index])=[Index] and [Unit]<>_unit then "Changed units" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Position_change", each let 
_filter1=Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID]),
_filter2=Table.SelectRows(_filter1,(x)=>x[Index]=List.Max(_filter1[Index])-1),
_position=List.Max(_filter2[Position]),
_judge=if [Position]>_position then "Promoted" else null
in  if [Index]=List.Max(_filter1[Index]) and _position>[Position]  then " "&"Position error"  else if [Index]=List.Max(_filter1[Index]) and _position<[Position] then _judge else null),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Position_change", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Position_change.1", "Position_change.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Position_change.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"Position_change.1", type text}, {"Position_change.2", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Date_error", each let 
_filter1=Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID] and x[Index]=[Index]-1),
_date=List.Max(_filter1[Start Date])
in  if [Index]<>1  and _date>[Start Date] then "Dates error" else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "If rerair", each let a=Table.RowCount(Table.SelectRows(#"Added Custom2",(x)=>x[ID]=[ID] and (x[Position_change.2]<>null or x[Date_error]<>null)))
in if [ended]="Yes" and a=0 and [Index]<>1 then "Retired" else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Changes", each let a=Table.SelectRows(#"Added Custom3",(x)=>x[ID]=[ID])
in if List.Max(a[If rerair])<>null then List.Max(a[If rerair]) else if List.Max(a[Unit_change])<>null and  List.Max(a[Position_change.1])<>null then List.Max(a[Unit_change]) & " and " & List.Max(a[Position_change.1]) else  List.Max({List.Max(a[Unit_change]),List.Max(a[Position_change.1])})),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom4",null,"",Replacer.ReplaceValue,{"Position_change.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Date_error"}),
    #"Added Custom5" = Table.AddColumn(#"Replaced Value1", "Error", each let a=Table.SelectRows(#"Replaced Value1",(x)=>x[ID]=[ID])
in List.Max(a[Position_change.2])&" "&List.Max(a[Date_error])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Index", "Unit_change", "Position_change.1", "Position_change.2", "Date_error", "If rerair"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

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

[dufoq3 · Accepted solution]

Hi @Sharkybu, 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRSgRiQxDWN9Q3MjAyATIjU4uVYnWQFRiBMC4FSVATjBEK/PLB8hGRUfhtgChIwrQBaoCrmzuSBVgMgCiA2YDFiQgFRggnGiFs8PH1w28AQoERqh8xFRgDsQmmgpLU4hIkO4wN9A1Q/BkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Unit = _t, Position = _t, #"Start Date" = _t, ended = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"All", each _, type table}, 
     {"Status", each 
         [ v_position =  if Table.LastN(_, 1)[ended]{0} = "Yes" then "Retired" else
                         if Table.RowCount(_) = 1 then "null" else //needed for ID's only with 1 row
                         if Table.LastN(_, 2)[Position]{0} < Table.LastN(_, 2)[Position]{1} then "Promoted" else
                         if Table.LastN(_, 2)[Position]{0} > Table.LastN(_, 2)[Position]{1} then "Demoted" else null,
           v_unit = if Table.RowCount(_) = 1 then "null" else //needed for ID's only with 1 row
                    if Table.LastN(_, 2)[Unit]{0} <> Table.LastN(_, 2)[Unit]{1} then "Changed Units" else null,
           Changes = if v_position = "Retired" then "Retired" else
                     if v_position = "Promoted" and v_unit = "Changed Units" then "Promoted and changed units" else 
                     if v_position = "Promoted" then "Promoted" else
                     if v_unit = "Changed Units" then "Changed Units" else null,
           Data Mistakes = if v_position = "Demoted" and v_unit = "Changed Units" then "Position error" else 
                           if v_position = "Promoted" and v_unit = null then "Date error" else null
         ]  }}),
    #"Expanded Status" = Table.ExpandRecordColumn(#"Grouped Rows", "Status", {"v_position", "v_unit", "Changes", "Data Mistakes"}, {"v_position", "v_unit", "Changes", "Data Mistakes"})
in
    #"Expanded Status"

[Sharkybu]

Thank you. but it gives an error in cases where reserchers have only one row in which "ended"=no

[dufoq3]

Hi @Sharkybu, I've edited prev. code. Try it now. ID's with only 1 row are now marked as null.

[Sharkybu]

Thank you, the code is helping me a lot. 

[Sharkybu]

Thank you so much.

It worked but it has a problem in cases where reserchers have only one row.

[v-xinruzhu-msft · Accepted solution]

Hi @Sharkybu 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRSgRiQxDWN9Q3MjAyATIjU4uVYnWQFRiBMC4FSVATjBEK/PLB8hGRUfhtgChIwrQBaoCrmzuSBVgMgCiA2YDFiQgFRggnGiFs8PH1w28AQoERqh8xFRgDsQmmgrLilCT8wYCkAkdIQ1UkY5oB8kcsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Unit = _t, Position = _t, #"Start Date" = _t, ended = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Unit", type text}, {"Position", Int64.Type}, {"Start Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Unit", "Position", "Start Date", "ended", "Index"}, {"Unit", "Position", "Start Date", "ended", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Data", "Unit_change", each let 
_filter1=Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID]),
_filter2=Table.SelectRows(_filter1,(x)=>x[Index]=List.Max(_filter1[Index])-1),
_unit=List.Max(_filter2[Unit])
in if List.Max(_filter1[Index])=[Index] and [Unit]<>_unit then "Changed units" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Position_change", each let 
_filter1=Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID]),
_filter2=Table.SelectRows(_filter1,(x)=>x[Index]=List.Max(_filter1[Index])-1),
_position=List.Max(_filter2[Position]),
_judge=if [Position]>_position then "Promoted" else null
in  if [Index]=List.Max(_filter1[Index]) and _position>[Position]  then " "&"Position error"  else if [Index]=List.Max(_filter1[Index]) and _position<[Position] then _judge else null),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Position_change", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Position_change.1", "Position_change.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Position_change.1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"Position_change.1", type text}, {"Position_change.2", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Date_error", each let 
_filter1=Table.SelectRows(#"Expanded Data",(x)=>x[ID]=[ID] and x[Index]=[Index]-1),
_date=List.Max(_filter1[Start Date])
in  if [Index]<>1  and _date>[Start Date] then "Dates error" else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "If rerair", each let a=Table.RowCount(Table.SelectRows(#"Added Custom2",(x)=>x[ID]=[ID] and (x[Position_change.2]<>null or x[Date_error]<>null)))
in if [ended]="Yes" and a=0 and [Index]<>1 then "Retired" else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Changes", each let a=Table.SelectRows(#"Added Custom3",(x)=>x[ID]=[ID])
in if List.Max(a[If rerair])<>null then List.Max(a[If rerair]) else if List.Max(a[Unit_change])<>null and  List.Max(a[Position_change.1])<>null then List.Max(a[Unit_change]) & " and " & List.Max(a[Position_change.1]) else  List.Max({List.Max(a[Unit_change]),List.Max(a[Position_change.1])})),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom4",null,"",Replacer.ReplaceValue,{"Position_change.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"Date_error"}),
    #"Added Custom5" = Table.AddColumn(#"Replaced Value1", "Error", each let a=Table.SelectRows(#"Replaced Value1",(x)=>x[ID]=[ID])
in List.Max(a[Position_change.2])&" "&List.Max(a[Date_error])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Index", "Unit_change", "Position_change.1", "Position_change.2", "Date_error", "If rerair"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

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