Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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"
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"
Thank you. but it gives an error in cases where reserchers have only one row in which "ended"=no
Thank you, the code is helping me a lot.
Thank you so much.
It worked but it has a problem in cases where reserchers have only one row.
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.