Home » Track Org Role Changes

Track Org Role Changes

Create a corresponding row in result table if there is a change in either role or org. Also document what has changed – role or org Blank means no change. For ex. George – New Org is blank, hence no change. Hence, no row will be created for Org change.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 178
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Track Org Role Changes with Power Query

Power Query solution 1 for Track Org Role Changes, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.FromRows(
    List.TransformMany(
      Table.ToRows(Source), 
      each List.RemoveNulls({_{2}, _{4}}), 
      (i, _) =>
        let
          f = Number.From(i{4} = _)
        in
          {i{0}, {"Role", "Org"}{f}, {i{1}, i{3}}{f}} & {_}
    ), 
    {"Emp", "Change", "Old", "New"}
  )
in
  S
Power Query solution 2 for Track Org Role Changes, proposed by Kris Jaganah: 
let
  Source  = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Idx     = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  Unpivot = Table.UnpivotOtherColumns(Idx, {"Emp", "Index"}, "Change", "Value"), 
  OldNew  = Table.AddColumn(Unpivot, "Old New", each Text.BeforeDelimiter([Change], " ")), 
  Chang   = Table.TransformColumns(OldNew, {"Change", each Text.AfterDelimiter(_, " ")}), 
  Pivot   = Table.Pivot(Chang, List.Distinct(Chang[#"Old New"]), "Old New", "Value"), 
  Filter  = Table.SelectRows(Pivot, each ([New] <> null)), 
  Sort    = Table.Sort(Filter, {{"Index", Order.Ascending}, {"Change", Order.Descending}}), 
  Remove  = Table.RemoveColumns(Sort, {"Index"})
in
  Remove
Power Query solution 3 for Track Org Role Changes, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Record = Table.AddColumn(
    Source, 
    "R", 
    each [
      R1 = [Emp = [Emp], Change = "Role", Old = [Old Role], New = [New Role]], 
      R2 = [Emp = [Emp], Change = "Org", Old = [Old Org], New = [New Org]], 
      R  = (if [New Role] = null then {} else {R1}) & (if [New Org] = null then {} else {R2})
    ][R]
  ), 
  Return = Table.FromRecords(List.Combine(Record[R]))
in
  Return
Power Query solution 4 for Track Org Role Changes, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Emp"}, 
    {
      {
        "A", 
        each 
          let
            a = Table.RemoveColumns(_, "Emp"), 
            b = List.Transform(Table.ToColumns(a), each _{0}), 
            c = List.Split(b, 2), 
            d = {"Role", "Org"}, 
            e = Table.FromRows(
              List.Transform({0 .. List.Count(d) - 1}, each {d{_}} & c{_}), 
              {"Change", "Old", "New"}
            ), 
            f = Table.SelectRows(e, each [New] <> null)
          in
            f
      }
    }
  ), 
  Sol = Table.SelectRows(
    Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0})), 
    each [Change] <> null
  )
in
  Sol
Power Query solution 5 for Track Org Role Changes, proposed by Ramiro Ayala Chávez: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a = Table.Group(
    S, 
    {"Emp"}, 
    {{"G", each {"Role"} & [Old Role] & [New Role]}, {"H", each {"Org"} & [Old Org] & [New Org]}}
  ), 
  b = Table.AddColumn(a, "M", each Table.FromRows({[G], [H]}))[[Emp], [M]], 
  c = Table.TransformColumns(b, {"M", each Table.SelectRows(_, each [Column3] <> null)}), 
  Sol = Table.SelectRows(
    Table.ExpandTableColumn(c, "M", {"Column1", "Column2", "Column3"}, {"Change", "Old", "New"}), 
    each [Change] <> null
  )
in
  Sol
Power Query solution 6 for Track Org Role Changes, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData178"]}[Content], 
  Transform = List.Transform(
    Table.ToRows(Source), 
    each 
      let
        L = {{_{0}, "Role", _{1}, _{2}}, {_{0}, "Org", _{3}, _{4}}}
      in
        List.Select(L, each _{3} <> null)
  ), 
  Result = Table.FromRows(List.Combine(Transform), {"Emp", "Change", "Old", "New"})
in
  Result
Power Query solution 7 for Track Org Role Changes, proposed by Albert Cid Cañigueral: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  ndA = Table.RenameColumns(
    Table.AddColumn(Origen[[Emp], [Old Role], [New Role]], "Change", each "Role"), 
    {{"Old Role", "Old"}, {"New Role", "New"}}
  ), 
  ndB = Table.RenameColumns(
    Table.AddColumn(Origen[[Emp], [Old Org], [New Org]], "Change", each "Org"), 
    {{"Old Org", "Old"}, {"New Org", "New"}}
  ), 
  ndC = Table.Sort(
    Table.SelectRows(Table.Combine({ndA, ndB}), each [New] <> null and [New] <> ""), 
    {"Emp"}
  )[[Emp], [Change], [Old], [New]]
in
  ndC
Power Query solution 8 for Track Org Role Changes, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(
    S, 
    "C", 
    each Table.Transpose(Table.FromColumns(List.Split(List.Skip(Record.ToList(_), 1), 2)))
  ), 
  B = Table.SelectColumns(A, {"Emp", "C"}), 
  C = Table.ExpandTableColumn(B, "C", {"Column1", "Column2"}, {"Column1", "Column2"}), 
  D = Table.AddIndexColumn(C, "I", 1, 1, Int64.Type), 
  E = Table.AddColumn(D, "Change", each if Number.IsOdd([I]) then "Role" else "Org"), 
  F = Table.SelectRows(E, each ([Column2] <> null)), 
  G = Table.RenameColumns(F, {{"Column1", "Old"}, {"Column2", "New"}}), 
  H = Table.SelectColumns(G, {"Emp", "Change", "Old", "New"})
in
  H
Power Query solution 9 for Track Org Role Changes, proposed by Peter Tholstrup: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  getChange = (a, b) => if a <> null then b else null, 
  getDetails = (_) =>
    if [Change] = "Role" then
      {[Old Role], [New Role]}
    else if [Change] = "Org" then
      {[Old Org], [New Org]}
    else
      null, 
  change = Table.AddColumn(
    Source, 
    "Change", 
    each List.RemoveNulls({getChange([New Role], "Role"), getChange([New Org], "Org")})
  ), 
  expand = Table.ExpandListColumn(change, "Change"), 
  details = Table.AddColumn(
    Table.SelectRows(expand, each [Change] <> null), 
    "Details", 
    each [Old = getDetails(_){0}, New = getDetails(_){1}]
  ), 
  result = Table.ExpandRecordColumn(
    Table.SelectColumns(details, {"Emp", "Change", "Details"}), 
    "Details", 
    {"Old", "New"}
  )
in
  result
Power Query solution 10 for Track Org Role Changes, proposed by Yaroslav Drohomyretskyi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Emp"}, "Type", "Value"), 
  Split = Table.SplitColumn(
    Unpivot, 
    "Type", 
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
    {"Type", "Change"}
  ), 
  Pivot = Table.Pivot(Split, List.Distinct(Split[Type]), "Type", "Value"), 
  Filter = Table.SelectRows(Pivot, each [New] <> null and [New] <> "")
in
  Filter
Power Query solution 11 for Track Org Role Changes, proposed by Mihai Radu O: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  sol = Table.SelectRows(
    Table.SelectColumns(
      Table.ExpandTableColumn(
        Table.AddColumn(
          Source, 
          "Custom", 
          each 
            let
              rol = Record.ToList(
                Record.SelectFields(
                  _, 
                  List.Select(
                    Table.ColumnNames(Source), 
                    each Text.Contains(_, "role", Comparer.OrdinalIgnoreCase)
                  )
                )
              ), 
              org = Record.ToList(
                Record.SelectFields(
                  _, 
                  List.Select(
                    Table.ColumnNames(Source), 
                    each Text.Contains(_, "org", Comparer.OrdinalIgnoreCase)
                  )
                )
              ), 
              chRol = if List.NonNullCount(rol) = List.Count(rol) then "role" else null, 
              chOrg = if List.NonNullCount(org) = List.Count(org) then "org" else null, 
              tbl = Table.FromRows({{chRol} & rol, {chOrg} & org}, {"Change", "Old", "New"})
            in
              tbl
        ), 
        "Custom", 
        {"Change", "Old", "New"}
      ), 
      {"Emp", "Change", "Old", "New"}
    ), 
    each [Change] <> null
  )
in
  sol

Solving the challenge of Track Org Role Changes with Excel

Excel solution 1 for Track Org Role Changes, proposed by Bo Rydobon 🇹🇭: 
=LET(r,REDUCE(H1:K1,A2:A5,LAMBDA(a,v,LET(b,HSTACK({"Role";"Org"},WRAPROWS(TAKE(E2:v,-1,-4),2)),VSTACK(a,IFNA(HSTACK(v,b),v))))),FILTER(r,DROP(r,,3)>0))
Excel solution 2 for Track Org Role Changes, proposed by محمد حلمي: 
=LET(a,A2:A5,r,LAMBDA(x,w,v,
IFNA(FILTER(HSTACK(a,v,w,x),x>""),v)),
c,VSTACK(r(C2:C5,B2:B5,"Role"),r(E2:E5,D2:D5,"Org")),
SORTBY(c,XMATCH(TAKE(c,,1),a)))
Excel solution 3 for Track Org Role Changes, proposed by Julian Poeltl: 
=LET(T,A2:E5,N,TAKE(T,,1),OR,CHOOSECOLS(T,2),NR,CHOOSECOLS(T,3),OO,CHOOSECOLS(T,4),NO,TAKE(T,,-1),OOR,FILTER(OO,NO<>0),NOR,FILTER(NO,NO<>0),ORR,FILTER(OR,NR<>0),NRR,FILTER(NR,NR<>0),NaO,FILTER(N,NO<>0),NaR,FILTER(N,NR<>0),RC,COUNTA(NaR),OC,COUNTA(NaO),R,VSTACK(HSTACK(NaR,IFERROR(SEQUENCE(RC)*A,"Role"),ORR,NRR),HSTACK(NaO,IFERROR(SEQUENCE(OC)*A,"Org"),OOR,NOR)),VSTACK(HSTACK("Emp","Change","Old","New"),SORT(R,1,1)))
(different sorting)
Excel solution 4 for Track Org Role Changes, proposed by Sunny Baggu: 
=LET(
 _e1, LAMBDA(x, y, (x <> "") * (y <> "") * (x <> y)),
 _c1, _e1(B2:B5, C2:C5),
 _c2, _e1(D2:D5, E2:E5),
 _r1, SEQUENCE(SUM(_c1)),
 _r2, SEQUENCE(SUM(_c2)),
 _t1, INDEX(IFNA(HSTACK("Role", FILTER(A2:E5, _c1)), "Role"), _r1, {2, 1, 3, 4}),
 _t2, INDEX(IFNA(HSTACK("Org", FILTER(A2:E5, _c2)), "Org"), _r2, {2, 1, 5, 6}),
 SORT(VSTACK(_t1, _t2))
)
Excel solution 5 for Track Org Role Changes, proposed by Abdallah Ally: 
=True)
Excel solution 6 for Track Org Role Changes, proposed by Abdallah Ally: 
=LET(a,IFNA(REDUCE(0,A2:A5,LAMBDA(x,y,VSTACK(x,VSTACK(HSTACK( y,"Role",OFFSET(y,,1,1,2)),HSTACK(y,"Org",OFFSET(y,,3,1,2)))))),0),FILTER(a,BYROW(a,LAMBDA(x,AND(x>0)))))
Excel solution 7 for Track Org Role Changes, proposed by 🇵🇪 Ned Navarrete C.: 
=v),2),VSTACK(c,IFNA(HSTACK(v,REPT({"Role";"Org"},BYROW(p>9,LAMBDA(r,AND(r)))),p),v))))),FILTER(m,INDEX(m,,2)<>""))
Excel solution 8 for Track Org Role Changes, proposed by Md. Zohurul Islam: 
=LET(u,A2:A5,v,B2:C5,w,D2:E5,
hdr,HSTACK("Emp","Change","Old","New"),
f,LAMBDA(a,b,c,FILTER(a,b=c)),
p,DROP(REDUCE("",UNIQUE(u),LAMBDA(x,y,LET(
 a,HSTACK("Role",f(v,u,y)),
 b,HSTACK("Org",f(w,u,y)),
 c,VSTACK(a,b),
 d,IF(COUNTA(f(w,u,y))>1,c,a),
 e,VSTACK(x,IFNA(HSTACK(y,d),y)),
 e))),1),
q,VSTACK(hdr,FILTER(p,TAKE(p,,-1)<>0)),
q)
Excel solution 9 for Track Org Role Changes, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=VSTACK({"Emp","Change","Old","New"},LET(p,AND(B2:B5<>C2:C5,D2:D5<>E2:E5),i,LET(j,LET(e,DROP(TEXTSPLIT(TEXTJOIN(,FALSE,MAP(IF(p,C2:C5&","&E2:E5,""),LAMBDA(m,TEXTJOIN(",",FALSE,TEXTSPLIT(m,","))))&"/"),",","/",FALSE),-1),d,DROP(TEXTSPLIT(TEXTJOIN(,FALSE,MAP(IF(p,B2:B5&","&D2:D5,""),LAMBDA(m,TEXTJOIN(",",FALSE,TEXTSPLIT(m,","))))&"/"),",","/",FALSE),-1),MAP(TAKE(d,,2),TAKE(d<>"",,2),TAKE(e,,2),TAKE(e<>"",,2),LAMBDA(x,y,z,t,IF(AND(y=TRUE,t=TRUE),x&","&z)))),LET(a,IFERROR(TEXTSPLIT(TEXTJOIN(,,IF(j<>FALSE,j,"")&"/"),",","/"),""),FILTER(a,CHOOSECOLS(a,2)<>""))),HSTACK(MAP(TAKE(i,,1),LAMBDA(j,TEXTJOIN(",",,IF(j=A2:E5,A2:A5,"")))),MAP(MAP(TAKE(i,,1),LAMBDA(q,TEXTJOIN(",",,IF(ISNUMBER(IF(q=B2:E5,COLUMN(B2:E5),"")),B1:E1,"")))),LAMBDA(w,DROP(TEXTSPLIT(w," "),,1))),i)))
Excel solution 10 for Track Org Role Changes, proposed by LUIS FLORENTINO COUTO CORTEGOSO: 
=LET(a,A2:A5,e,TOCOL(HSTACK(a,a)),b,WRAPROWS(TOROW(B2:E5),2),VSTACK(H1:K1,FILTER(HSTACK(e,IF(MOD(SEQUENCE(ROWS(b)),2),"Role","Org"),b),DROP(b,,1)>0)))

Solving the challenge of Track Org Role Changes with Python

Python solution 1 for Track Org Role Changes, proposed by Konrad Gryczan, PhD: 
import pandas as pd
input = pd.read_excel("PQ_Challenge_178.xlsx", usecols = "A:E", nrows=5)
test = pd.read_excel("PQ_Challenge_178.xlsx", usecols="H:K", nrows=5, names=["Emp", "Change", "Old", "New"])
test = test.sort_values(by = ["Emp", "Change"]).reset_index(drop=True)
result = input.melt(id_vars="Emp", var_name="Change", value_name="Value")
result[["Type", "Change"]] = result["Change"].str.split(" ", expand=True) 
result = result.pivot(index=["Emp", "Change"], columns="Type", values="Value").dropna().reset_index()
result = result[["Emp", "Change", "Old", "New"]] 
result.columns.name = None
result = result.sort_values(by=["Emp", "Change"]).reset_index(drop=True)
print(result.equals(test)) # True

Solving the challenge of Track Org Role Changes with Python in Excel

Python in Excel solution 1 for Track Org Role Changes, proposed by Abdallah Ally: 
import pandas as pd
file_path = 'PQ_Challenge_178.xlsx'
df = pd.read_excel(file_path, usecols='A:E')
# Perform data transformation and cleansing
values = []
for i in df.index:
 values.append(list(df.iloc[i, [0, 1, 2]]))
 values[-1].insert(1, 'Role')
 values.ap&pend(list(df.iloc[i, [0, 3, 4]]))
 values[-1].insert(1, 'Org')
df = pd.DataFrame(values, columns=['Emp', 'Change', 'Old', 'New']).dropna()
print(df)

Solving the challenge of Track Org Role Changes with R

R solution 1 for Track Org Role Changes, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_178.xlsx", range = "A1:E5")
test = read_excel("Power Query/PQ_Challenge_178.xlsx", range = "H1:K5")
result = input %>%
 pivot_longer(-Emp, names_to = "Change", values_to = "Value") %>%
 separate(Change, into = c("Type", "Change"), sep = " ") %>%
 pivot_wider(names_from = Type, values_from = Value) %>%
 drop_na()
R solution 3 for Track Org Role Changes, proposed by Anil Kumar Goyal: 
library(readxl)
library(janitor)
library(tidyverse)
df <- read_xlsx("PQ/PQ_Challenge_178.xlsx", range = cell_cols(LETTERS[1:5])) %>% 
 clean_names(case = "title")
df |> 
 pivot_longer(-Emp, names_to = c(".value", "Change"), names_sep = " ") |> 
 na.omit()

&&

Leave a Reply