Home » Log Changes Within Group

Log Changes Within Group

Except the Group column, for every other column, log the value within a group if value changes. For Power Query solution, there will be lots of columns like this but only 4 columns are given, so your solution should be dynamic to accommodate any number of columns.

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

Solving the challenge of Log Changes Within Group with Power Query

Power Query solution 1 for Log Changes Within Group, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  C = Table.ToColumns(Source), 
  R = List.Reverse, 
  S = Table.FromColumns(
    {C{0}}
      & List.Transform(
        List.Skip(C), 
        (o) =>
          R(
            List.Transform(
              List.Positions(o), 
              (i) =>
                if (R(C{0}){i} <> R(C{0}){i + 1}? ?? "") or R(o){i} = R(o){i + 1} then
                  null
                else
                  R(o){i}
            )
          )
      ), 
    Table.ColumnNames(Source)
  )
in
  S
Power Query solution 2 for Log Changes Within Group, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Group"}, 
    {
      {
        "All", 
        each 
          let
            a = _, 
            b = List.Skip(Table.ToColumns(a)), 
            c = List.Transform(
              b, 
              each List.Transform(
                {0 .. List.Count(_) - 1}, 
                (x) => try if _{x - 1} = _{x} then null else _{x} otherwise null
              )
            ), 
            d = Table.FromColumns(c, List.Skip(Table.ColumnNames(a)))
          in
            d
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0}))
in
  Sol
Power Query solution 3 for Log Changes Within Group, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"Group"}, 
    {
      {
        "Contagem", 
        each 
          let
            a = Table.RemoveColumns(_, {"Group"}), 
            b = Table.ToColumns(a), 
            c = Table.FromColumns(
              List.Transform(
                b, 
                each List.Transform(
                  {0 .. List.Count(_) - 1}, 
                  (x) => try if _{x - 1} = _{x} then null else _{x} otherwise null
                )
              ), 
              Table.ColumnNames(a)
            )
          in
            c
      }
    }
  ), 
  res = Table.ExpandTableColumn(gp, "Contagem", Table.ColumnNames(gp[Contagem]{0}))
in
  res
Power Query solution 4 for Log Changes Within Group, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData157"]}[Content], 
  CN = Table.ColumnNames(Source), 
  Group = Table.Group(
    Source, 
    "Group", 
    {
      "All", 
      each 
        let
          _C = Table.ToColumns(_), 
          _T = List.Accumulate(
            List.Skip(_C), 
            {_C{0}}, 
            (s, c) =>
              let
                _Z = List.Zip({c, {c{0}} & List.RemoveLastN(c)}), 
                _T = List.Accumulate(_Z, {}, (s, c) => s & {if (c{0} = c{1}) then null else c{0}})
              in
                s & {_T}
          )
        in
          Table.FromColumns(_T, CN)
    }
  ), 
  Result = Table.Combine(Group[All])
in
  Result
Power Query solution 5 for Log Changes Within Group, proposed by Luke Jarych: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ColumnNames = List.Skip(Table.ColumnNames(Source)), 
  GroupedRows = Table.Group(
    Source, 
    {"Group"}, 
    {
      {
        "All", 
        each 
          let
            a = List.Skip(Table.ToColumns(_)), 
            b = List.Transform(
              a, 
              each 
                let
                  s = _, 
                  g = List.Generate(
                    () => [x = null, i = 0, c = List.Count(s)], 
                    each [i] < [c], 
                    each [i = [i] + 1, x = if s{i} <> s{i - 1} then s{i} else null, c = [c]], 
                    each [x]
                  )
                in
                  g
            ), 
            f = Table.FromColumns(b, ColumnNames)
          in
            f
      }
    }
  ), 
  ExpandedAll = Table.ExpandTableColumn(GroupedRows, "All", ColumnNames, ColumnNames)
in
  ExpandedAll
Power Query solution 6 for Log Changes Within Group, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped" = Table.Group(
    Source, 
    {"Group"}, 
    {
      {
        "Count", 
        each Table.FromColumns(
          List.Transform(
            Table.ToColumns(Table.RemoveColumns(_, {"Group"})), 
            each List.Accumulate(
              _, 
              {}, 
              (a, v) =>
                if List.Count(a) = 0 or (_{List.Count(a)} = _{List.Count(a) - 1}) then
                  List.Combine({a, {null}})
                else
                  List.Combine({a, {v}})
            )
          ), 
          List.Skip(Table.ColumnNames(Source))
        )
      }
    }
  ), 
  #"Expanded" = Table.ExpandTableColumn(
    #"Grouped", 
    "Count", 
    {"Number1", "State", "Number2", "Code"}
  )
in
  #"Expanded"
Power Query solution 7 for Log Changes Within Group, proposed by Glyn Willis: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Grouped Rows" = Table.Group(
    Source, 
    {"Group"}, 
    {
      {
        "g", 
        each 
          let
            c = List.RemoveMatchingItems(Table.ColumnNames(_), {"Group"})
          in
            Table.FromColumns(
              List.Transform(
                c, 
                (y) =>
                  Table.AddColumn(
                    Table.FromColumns(
                      let
                        tc = Table.Column(_, y)
                      in
                        {tc, {null} & List.RemoveLastN(tc, 1)}
                    ), 
                    "d", 
                    (x) => if x[Column1] = (x[Column2] ?? x[Column1]) then null else x[Column1]
                  )[d]
              ), 
              c
            ), 
        type any
      }
    }
  ), 
  #"Expanded State" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "g", 
    List.Union(List.Transform(#"Grouped Rows"[g], (x) => Table.ColumnNames(x)))
  )
in
  #"Expanded State"

Solving the challenge of Log Changes Within Group with Excel

Excel solution 1 for Log Changes Within Group, proposed by محمد حلمي: 
=HSTACK(A2:A31,IF(A2:A31=A1:A30,IF(B2:E31=B1:E30,"",B2:E31),""))
Excel solution 2 for Log Changes Within Group, proposed by محمد حلمي: 
=REDUCE(A1:E1,UNIQUE(A2:A31),LAMBDA(a,d,LET(i,FILTER(A2:E31,A2:A31=d),e,DROP(i,1,1),IFNA(VSTACK(a,HSTACK(TAKE(i,,1),VSTACK("",IF(e=DROP(i,-1,1),"",e)))),""))))
Excel solution 3 for Log Changes Within Group, proposed by Oscar Mendez Roca Farell: 
=REDUCE(A2:E2, UNIQUE(A3:A32), LAMBDA(i, x, LET(_f, FILTER(B3:E32,A3:A32=x),_v, VSTACK(TAKE(_f, 1),_f), VSTACK(i, IFNA(HSTACK(x, REPT(_f, _f<>DROP(_v, -1))), x)))))
Excel solution 4 for Log Changes Within Group, proposed by Sunny Baggu: 
=LET(
 _ug, UNIQUE(A2:A31),
 REDUCE(
 A1:E1,
 _ug,
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _tbl, FILTER(B2:E31, A2:A31 = y),
 HSTACK(
 FILTER(A2:A31, A2:A31 = y),
 IF(
 DROP(
 REDUCE("", SEQUENCE(COLUMNS(B1:E1)), LAMBDA(a, v, HSTACK(a, NOT(VSTACK(TRUE, DROP(INDEX(_tbl, , v), -1) = DROP(INDEX(_tbl, , v), 1)))))),
 ,
 1
 ),
 _tbl,
 ""
 )
 )
 )
 )
 )
 )
)
Excel solution 5 for Log Changes Within Group, proposed by LEONARD OCHEA 🇷🇴: 
=LET(t,A1:E31,g,TAKE(t,,1),E,LAMBDA(x,y,z,DROP(x,y,z)),d,E(t,,1),HSTACK(g,VSTACK(E(TAKE(t,1),,1),IF((E(d,-1,)<>E(d,1,))*(E(g,-1,)=E(g,1,)),E(d,1,),""))))
Excel solution 6 for Log Changes Within Group, proposed by LEONARD OCHEA 🇷🇴: 
=> table A1:E31 & header included
=LET(t,A1:E31,f,ROWS(t)-1,I,LAMBDA(x,INDEX(t,x,1)),E,LAMBDA(y,DROP(INDEX(t,y,),,1)),REDUCE(TAKE(t,1),SEQUENCE(f),LAMBDA(a,b,VSTACK(a,HSTACK(I(b+1),IF((I(b)=I(b+1))*(E(b)<>E(b+1)),E(b+1),""))))))

Solving the challenge of Log Changes Within Group with R

R solution 1 for Log Changes Within Group, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_157.xlsx", range = "A1:E31")
test = read_excel("Power Query/PQ_Challenge_157.xlsx", range = "G1:K31") %>%
 mutate(across(everything(), as.character))
log_changes <- function(data) {
 data %>%
 mutate(across(everything(), as.character)) %>%
 group_by(Group) %>%
 mutate(across(everything(),
 ~if_else(lag(.x) != .x & !is.na(lag(.x)), .x, NA_character_))) %>%
 ungroup()
}
result = log_changes(input)

&&&

Leave a Reply