Home » Fill Down and Up by Customer

Fill Down and Up by Customer

Fill down / up all 4 columns for different customer IDs. For any Customer, only one of the cells in respective column is populated. That is how you need to identify the first row and last row for a Customer. (I have shaded this in different colors to provide clarity) Type column will be suffixed with 1, 2, 3 after fill down / up for a Customer.

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

Solving the challenge of Fill Down and Up by Customer with Power Query

Power Query solution 1 for Fill Down and Up by Customer, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  cn = Table.ColumnNames(Source), 
  Ans = Table.Combine(
    Table.Group(
      Table.FromColumns(
        Table.ToColumns(Source)
          & {
            List.Transform(
              List.Accumulate(
                Table.ToRows(Source), 
                {}, 
                (s, l) => s & {List.Last(s, 0) + List.NonNullCount(l)}
              ), 
              each Number.RoundUp(_ / 4)
            )
          }, 
        cn & {"G"}
      ), 
      "G", 
      {
        "T", 
        each Table.SelectColumns(
          Table.ReplaceValue(
            Table.AddIndexColumn(Table.FillUp(Table.FillDown(_, cn), cn), "i", 1), 
            "", 
            each Text.From([i]), 
            (c, o, n) => c & n, 
            {"Type"}
          ), 
          cn
        )
      }
    )[T]
  )
in
  Ans
Power Query solution 2 for Fill Down and Up by Customer, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  R = List.Reverse, 
  F = each List.Accumulate(
    _, 
    {}, 
    (s, c) =>
      let
        l = List.Last(s, c), 
        v = each c{_} ?? l{_}
      in
        s & {if List.Count(List.Difference(l, c)) = 4 then c else {v(0), v(1), v(2), v(3)}}
  ), 
  S = Table.FromRows(
    List.Accumulate(
      F(R(F(R(Table.ToRows(Source))))), 
      {}, 
      (s, c) =>
        let
          l = List.Last(s, {0})
        in
          s
            & {
              List.RemoveLastN(c)
                & {
                  c{3}
                    & (
                      if l{0} <> c{0} then
                        "1"
                      else
                        Text.From(Number.From(Text.Select(l{3}, {"0" .. "9"})) + 1)
                    )
                }
            }
    ), 
    Table.ColumnNames(Source)
  )
in
  S
Power Query solution 3 for Fill Down and Up by Customer, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Col = Table.AddColumn(Source, "Custom", each List.Count(List.RemoveNulls(Record.ToList(_)))), 
  Lista = List.Skip(
    List.Accumulate(
      Col[Custom], 
      {0}, 
      (s, c) => s & {if List.Last(s) + c <= 4 then List.Last(s) + c else c}
    )
  ), 
  Gen = List.Generate(
    () => [x = 0, y = 1], 
    each [x] < List.Count(Lista), 
    each [x = [x] + 1, y = if Lista{[x]} < 4 then [y] + 1 else 1], 
    each [y]
  ), 
  Rep = List.Transform(List.Select(List.Zip({Lista, Gen}), each _{0} = 4), each _{1}), 
  List2 = List.Combine(
    List.Transform(
      List.Zip(
        {
          Table.ToRows(
            Table.FromColumns(List.Transform(Table.ToColumns(Source), each List.RemoveNulls(_)))
          ), 
          Rep
        }
      ), 
      each List.Repeat({_{0}}, _{1})
    )
  ), 
  Sol = Table.FromRows(
    List.Transform(
      {0 .. List.Count(List2) - 1}, 
      each List.RemoveLastN(List2{_}) & {List.Last(List2{_}) & Text.From(Gen{_})}
    ), 
    Table.ColumnNames(Source)
  )
in
  Sol
Power Query solution 4 for Fill Down and Up by Customer, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Gen = List.Skip(
    List.Generate(
      () => [x = 0, z = 0, y = {}], 
      each [z] <= Table.RowCount(Source), 
      each [
        x = if List.Count([y]) < 4 then [x] + 1 else 1, 
        z = [z] + 1, 
        y = 
          if List.Count([y]) < 4 then
            List.RemoveNulls([y] & Table.ToRows(Source){[z]})
          else
            List.RemoveNulls(Table.ToRows(Source){[z]})
      ], 
      each [[y], [x]]
    )
  ), 
  Sel = List.Transform(
    List.Select(List.Transform(Gen, each Record.ToList(_)), each List.Count(_{0}) = 4), 
    each _{1}
  ), 
  Zip = List.Zip(
    {Table.ToRows(Table.FromColumns(List.Transform(Table.ToColumns(Source), List.RemoveNulls)))}
      & {Sel}
  ), 
  Tabla = Table.FromRows(
    List.Combine(List.Transform(Zip, each List.Repeat({_{0}}, _{1}))), 
    Table.ColumnNames(Source)
  ), 
  Sol = Table.ExpandListColumn(
    Table.Group(
      Tabla, 
      List.RemoveLastN(Table.ColumnNames(Source)), 
      {{"Type", each List.Transform({1 .. List.Count([Type])}, (x) => [Type]{0} & Text.From(x))}}
    ), 
    "Type"
  )
in
  Sol
Power Query solution 5 for Fill Down and Up by Customer, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  pa = Table.FillDown(Fonte, {"Cust ID"}), 
  gp = Table.ToColumns(Fonte)
    & {
      Table.FillDown(
        Table.Combine(
          Table.Group(
            pa, 
            {"Cust ID"}, 
            {
              {
                "Contagem", 
                each Table.RemoveColumns(Table.FillUp(_, Table.ColumnNames(Fonte)), {"Cust ID"})[
                  [Type]
                ]
              }
            }
          )[Contagem]
        ), 
        {"Type"}
      )[Type]
    }, 
  tab = Table.FromColumns(gp, Table.ColumnNames(Fonte) & {"val"}), 
  g = Table.Group(
    tab, 
    {"val"}, 
    {
      {
        "Contagem", 
        each Table.AddIndexColumn(
          Table.FillUp(Table.FillDown(_, Table.ColumnNames(Fonte)), Table.ColumnNames(Fonte)), 
          "Ind", 
          1, 
          1
        )
      }
    }, 
    GroupKind.Local
  )[Contagem], 
  res = Table.Combine(
    List.Transform(
      g, 
      each Table.RemoveColumns(
        Table.AddColumn(_, "Tipo", each [Type] & Text.From([Ind])), 
        {"val", "Type", "Ind"}
      )
    )
  )
in
  res
Power Query solution 6 for Fill Down and Up by Customer, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ToCols = Table.ToColumns(Source), 
  ColNames = Table.ColumnNames(Source), 
  Output = Table.Combine(
    Table.Group(
      Table.FromColumns(
        ToCols
          & {
            List.Transform(
              List.Zip(
                List.Transform(
                  ToCols, 
                  each List.Skip(
                    List.Accumulate(_, {0}, (x, y) => x & {List.Last(x) + Number.From(y <> null)})
                  )
                )
              ), 
              List.Max
            )
          }, 
        ColNames & {"Group"}
      ), 
      {"Group"}, 
      {
        {
          "All", 
          each Table.SelectColumns(
            Table.ReplaceValue(
              Table.AddIndexColumn(Table.FillUp(Table.FillDown(_, ColNames), ColNames), "Index", 1), 
              each [Index], 
              null, 
              (a, b, c) => a & Text.From(b), 
              {"Type"}
            ), 
            ColNames
          )
        }
      }
    )[All]
  )
in
  Output
Power Query solution 7 for Fill Down and Up by Customer, proposed by Ramiro Ayala Chávez: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = List.Transform(Table.ToColumns(Origen), each List.RemoveNulls(_)), 
  b = Table.ToRows(Table.FromColumns(a)), 
  c = {0 .. List.Count(b) - 1}, 
  d = {3, 4, 3, 5, 1}, 
  e = List.Transform(c, each List.Split(List.Repeat(b{_}, d{_}), 4)), 
  f = List.Transform(
    e, 
    each List.Transform(_, each Table.Transpose(Table.FromList(_, Splitter.SplitByNothing())))
  ), 
  g = List.Transform(f, each Table.Combine(_)), 
  h = Table.Combine(List.Transform(g, each Table.AddIndexColumn(_, "I", 1))), 
  i = List.Zip({Table.ColumnNames(h), Table.ColumnNames(Origen) & {"I"}}), 
  j = Table.TransformColumnTypes(Table.RenameColumns(h, i), {{"I", type text}}), 
  Sol = Table.RenameColumns(
    Table.RemoveColumns(Table.AddColumn(j, "J", each [Type] & [I]), {"Type", "I"}), 
    {{"J", "Type"}}
  )
in
  Sol
Power Query solution 8 for Fill Down and Up by Customer, proposed by Eric Laforce: 
leted then add in the Result-List 
 1 innerTable with repeated records + suffix[Type] by added index 
2) Combine InnerTables from previous step
 
let
 Source = Excel.CurrentWorkbook(){[Name="tData147"]}[Content],
 CN = Table.ColumnNames(Source), NC = List.Count(CN), LVNull = List.Repeat({null}, NC),
 Transform = List.Accumulate(Table.ToRows(Source), [r={}, v=LVNull, i=0], (s,c)=>let
 _V = List.Transform(List.Zip({c, s[v]}), each _{0}??_{1}),
 _VC = List.NonNullCount(_V) = NC,
 _NewT = if (_VC=false) then null else let
 _R = Record.FromList(_V, CN),
 _T = Table.FromRecords(List.Repeat({_R}, s[i]+1)),
 _TI = Table.ReplaceValue(Table.AddIndexColumn(_T,"_idx_",1),
 "", each Text.From([_idx_]), (cur,old,new)=> cur&new, {"Type"})
 in Table.RemoveColumns(_TI, "_idx_")
 in if (_VC) then [r=s[r] & {_NewT}, v=LVNull, i=0] else [r=s[r], v=_V, i=s[i]+1] ),
 Result = Table.Combine(Transform[r])
in
 Result
Power Query solution 9 for Fill Down and Up by Customer, proposed by Albert Cid Cañigueral: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  ndConteo = Table.AddColumn(Origen, "Conteo", each List.Count(List.RemoveNulls(Record.ToList(_)))), 
  ndIndice = Table.AddIndexColumn(ndConteo, "Indice", 1), 
  ndAcumulado = Table.AddColumn(
    ndIndice, 
    "Acumulado", 
    each Number.RoundUp(List.Sum(List.Range(ndIndice[Conteo], 0, [Indice])) / 4)
  ), 
  ndAgrupo = Table.Group(
    ndAcumulado, 
    "Acumulado", 
    {"Grupos", each [[Cust ID], [Cust Name], [Amount], [Type]]}
  ), 
  ndColIndice = Table.AddColumn(
    ndAgrupo, 
    "Tablas", 
    each 
      let
        a = Table.AddIndexColumn([Grupos], "Indice", 1), 
        b = Table.FillDown(a, {"Cust ID", "Cust Name", "Amount", "Type"}), 
        c = Table.FillUp(b, {"Cust ID", "Cust Name", "Amount", "Type"})
      in
        c
  ), 
  ndCombinar = Table.Combine(ndColIndice[Tablas]), 
  ndCombinoCols = Table.CombineColumns(
    Table.TransformColumnTypes(ndCombinar, {{"Indice", type text}}, "es-ES"), 
    {"Type", "Indice"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Type"
  )
in
  ndCombinoCols
Power Query solution 10 for Fill Down and Up by Customer, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  No = List.Count(Table.ColumnNames(S)), 
  A = Table.AddColumn(S, "N", each List.NonNullCount(Record.ToList(_))), 
  B = Table.AddIndexColumn(A, "I", 1, 1, Int64.Type), 
  C = Table.AddColumn(
    B, 
    "N2", 
    each List.Accumulate(
      List.Range(B[N], 0, [I]), 
      0, 
      (S, C) => if (S + C) < No + 1 then S + C else C
    )
  ), 
  D = Table.AddColumn(C, "G", each if [N2] = 4 then [I] else null), 
  E = Table.FillUp(D, {"G"}), 
  F = Table.Group(E, {"G"}, {{"X", each _}}), 
  G = Table.AddColumn(
    F, 
    "X2", 
    each Table.AddIndexColumn(
      Table.FillUp(Table.FillDown([X], Table.ColumnNames([X])), Table.ColumnNames([X])), 
      "Ind", 
      1, 
      1
    )
  ), 
  H = Table.SelectColumns(G, {"X2"}), 
  I = Table.ExpandTableColumn(
    H, 
    "X2", 
    {"Cust ID", "Cust Name", "Amount", "Type", "Ind"}, 
    {"Cust ID", "Cust Name", "Amount", "Type", "Ind"}
  ), 
  J = Table.CombineColumns(
    Table.TransformColumnTypes(I, {{"Ind", type text}}, "en-US"), 
    {"Type", "Ind"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Type.1"
  )
in
  J
Power Query solution 11 for Fill Down and Up by Customer, proposed by Mihai Radu O: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  tblColumnN = Table.ColumnNames(Source), 
  NrWordsRow = Table.AddColumn(
    Source, 
    "NrWords", 
    each List.NonNullCount(List.Transform(Record.FieldValues(_), Text.From))
  ), 
  index = Table.AddIndexColumn(NrWordsRow, "Index", 1, 1, Int64.Type), 
  Limits = Table.AddColumn(
    index, 
    "L", 
    each 
      let
        rt = List.Sum(List.FirstN(index[NrWords], [Index])), 
        a  = if rt / 4 = Number.IntegerDivide(rt, 4) then rt / 4 else null
      in
        a
  ), 
  #"Filled Up" = Table.FillUp(Limits, {"L"}), 
  #"Grouped Rows" = Table.Combine(
    Table.Group(
      #"Filled Up", 
      {"L"}, 
      {
        {
          "all", 
          each 
            let
              a = Table.FillUp(Table.FillDown(_, tblColumnN), tblColumnN)[
                [Cust ID], 
                [Cust Name], 
                [Amount], 
                [Type]
              ], 
              b = Table.AddIndexColumn(a, "Index", 1), 
              c = Table.CombineColumns(
                Table.TransformColumnTypes(b, {{"Index", type text}}), 
                {"Type", "Index"}, 
                Combiner.CombineTextByDelimiter(""), 
                "Type"
              )
            in
              c
        }
      }
    )[all]
  )
in
  #"Grouped Rows"
Power Query solution 12 for Fill Down and Up by Customer, proposed by Sandeep Marwal: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each List.Count(List.RemoveNulls(Record.ToList(_)))
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), 
  #"Added Custom1" = Table.AddColumn(
    #"Added Index", 
    "Custom.1", 
    each List.Sum(List.FirstN(#"Added Custom"[Custom], [Index])) / 4
  ), 
  #"Inserted Round Up" = Table.AddColumn(
    #"Added Custom1", 
    "Round Up", 
    each Number.RoundUp([Custom.1]), 
    Int64.Type
  ), 
  #"Grouped Rows" = Table.Group(
    #"Inserted Round Up", 
    {"Round Up"}, 
    {
      {
        "Count", 
        each Table.AddIndexColumn(
          Table.FillUp(Table.FillDown(_, Table.ColumnNames(_)), Table.ColumnNames(_)), 
          "Index1", 
          1
        )
      }
    }
  )[[Count]], 
  #"Expanded Count" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Count", 
    {"Cust ID", "Cust Name", "Amount", "Type", "Index1"}, 
    {"Cust ID", "Cust Name", "Amount", "Type", "Index1"}
  ), 
  #"Merged Columns" = Table.CombineColumns(
    Table.TransformColumnTypes(#"Expanded Count", {{"Index1", type text}}, "en-IN"), 
    {"Type", "Index1"}, 
    Combiner.CombineTextByDelimiter("", QuoteStyle.None), 
    "Merged"
  )
in
  #"Merged Columns"
Power Query solution 13 for Fill Down and Up by Customer, proposed by Glyn Willis: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Renamed Columns" = Table.RenameColumns(Source,{{"Cust ID", "ID"}, {"Cust Name", "N"}, {"Amount", "A"}, {"Type", "T"}}),
 Custom1 = Table.FromColumns(List.Transform(Table.ToColumns(#"Renamed Columns"),List.RemoveNulls),Table.ColumnNames(#"Renamed Columns")),
 Custom2 = 
 let 
 b=Table.ToRecords(#"Renamed Columns"),
 c=List.Count(b)
 in
 Table.FromRecords(
 List.Generate(()=>b{0}&[i=0,r=false],each [i]
Power Query solution 14 for Fill Down and Up by Customer, proposed by Glyn Willis: 
let t=Table.Max(_,{"T"})[T] in List.Transform({1..Table.RowCount(_)},(x)=>t&Text.From(x)), type list}
 }, 
 GroupKind.Local, (x,y)=> Int64.From(y[r]))[[Cust ID],[Type]],
 #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Cust ID"}, Custom1, {"ID"}, "D", JoinKind.LeftOuter),
 #"Expanded D" = Table.ExpandTableColumn(#"Merged Queries", "D", {"N", "A"}, {"Cust Name", "Amount"})[[Cust ID],[&Cust Name],[Amount],[Type]],
 #"Expanded Type" = Table.ExpandListColumn(#"Expanded D", "Type")
in
 #"Expanded Type"
Power Query solution 15 for Fill Down and Up by Customer, proposed by Arden Nguyen, CPA: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Cust ID", Int64.Type}, {"Cust Name", type text}, {"Amount", Int64.Type}, {"Type", type text}}
  ), 
  fill_up = Table.FillUp(#"Changed Type", {"Cust ID", "Cust Name", "Amount", "Type"}), 
  group = Table.Group(
    fill_up, 
    {"Cust ID", "Cust Name", "Amount", "Type"}, 
    {
      {
        "Rows", 
        each 
          let
            ct = Table.RowCount(_), 
            fill = Table.Repeat(Table.FirstN(_, 1), ct), 
            result = Table.CombineColumns(
              Table.AddIndexColumn(fill, "Idx", 1), 
              {"Type", "Idx"}, 
              each Text.Combine(List.Transform(_, each Text.From(_))), 
              "Type"
            )
          in
            result
      }
    }, 
    GroupKind.Local, 
    (x, y) => Byte.From(List.Intersect({Record.ToList(x), Record.ToList(y)}) = {})
  ), 
  final = Table.Combine(group[Rows])
in
  final

Solving the challenge of Fill Down and Up by Customer with Excel

Excel solution 1 for Fill Down and Up by Customer, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:D17,n,MAP(TAKE(z,,-1),LAMBDA(a,ROUNDUP(COUNTA(A2:a)/4,))),h,A1:D1,
VSTACK(h,DROP(REDUCE(0,h,LAMBDA(c,i,LET(d,INDEX(TOCOL(XLOOKUP(i,h,z),3),n),
HSTACK(c,IF(i<>"Type",d,d&SCAN(0,n=DROP(VSTACK(0,n),-1),LAMBDA(a,v,a*v+1))))))),,1)))
Excel solution 2 for Fill Down and Up by Customer, proposed by محمد حلمي: 
=REDUCE(A1:D1,SEQUENCE(COUNT(A2:A17)),LAMBDA(C,V,VSTACK(C,LET(e,A2:D17,x,COLUMN(e),J,INDEX(DROP(
REDUCE(0,x,LAMBDA(a,d,HSTACK(a,FILTER(ROW(e),
INDEX(e,,d)>0)))),,1),V),m,MIN(J),
z,SEQUENCE(MAX(J)-m+1),b,BYCOL(INDEX(e,z+m-2,x),
LAMBDA(q,@SORT(q,,-1))),IF(z*(x=4),b&z,b)))))
Excel solution 3 for Fill Down and Up by Customer, proposed by Kris Jaganah: 
=LET(a,A2:D17,b,TAKE(a,,1),c,WRAPCOLS(TOCOL(IF(a="",1/0,a),3,1),COUNT(b)),d,COUNTA(b),e,SCAN("",MAP(b,LAMBDA(x,BYROW(XLOOKUP(x,TAKE(c,,1),c,""),ARRAYTOTEXT))),LAMBDA(v,w,IF(w="",v,w))),f,TEXTSPLIT(TEXTJOIN("@",,e),", ","@"),g,BYROW(--(TEXT(a,"0")=f),SUM),h,SEQUENCE(ROWS(a)),i,IF(g=0,XLOOKUP(h+1,h,e),e),j,REDUCE(A1:D1,i&h-XMATCH(i,i)+1,LAMBDA(y,z,VSTACK(y,TEXTSPLIT(z,", ")))),IFERROR(--j,j))

Solving the challenge of Fill Down and Up by Customer with Python

Python solution 2 for Fill Down and Up by Customer, proposed by Jan Willem Van Holst: 
import pandas as pd
"C:JWLENOVOPYTHONPower_Query_Challenge_147.csv"
df = pd.read_csv(r"C:JWLENOVOPYTHONPower_Query_Challenge_147.csv", sep=";")
df = df.fillna('')
inputList = [df[df.columns[x]].to_list() for x in range(4)]
valuesList = []
for i in inputList:
 valuesList.append([x for x in i if x!=""])
def noRows(seqNoCustomer):
 listOfElement = [elem[seqNoCustomer] for elem in valuesList]
 PosOfElem = [temp[i].index(listOfElement[i]) for i in range(4)]
 result = max(PosOfElem)-min(PosOfElem)+1
 return result
temp = inputList
listOfNumbersOfRows=[]
for i in range(5):
 numberOfRows = noRows(i)
 listOfNumbersOfRows.append(numberOfRows)
 temp = [elem[numberOfRows:] for elem in temp]
 
answer =[]
for i in range(5):
 for j in range(listOfNumbersOfRows[i]):
 answer.append([valuesList[0][i], valuesList[1][i], valuesList[2][i], valuesList[3][i]+str(j+1)])

Solving the challenge of Fill Down and Up by Customer with R

R solution 1 for Fill Down and Up by Customer, proposed by Konrad Gryczan, PhD: 
Holy Guacamole :D That was challenging. 
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_147.xlsx", range = "A1:D17")
test = read_excel("Power Query/PQ_Challenge_147.xlsx", range = "F1:I17") %>%
 janitor::clean_names()
reshape <- function(input) {
 input %>%
 janitor::clean_names() %>%
 mutate(nr = row_number()) %>%
 mutate(across(c(cust_id, cust_name, amount, type),
 ~ ifelse(is.na(.), NA, cumsum(!is.na(.))),
 .names = "index_{.col}"),
 max_index = pmax(index_cust_id, index_cust_name, index_amount, index_type, na.rm = TRUE)) %>%
 group_by(max_index) %>%
 mutate(across(c(cust_id, cust_name, amount, type),
 ~ max(., na.rm = TRUE)),
 min_row = min(nr, na.rm = TRUE),
 max_row = max(nr, na.rm = TRUE)) %>%
 ungroup() %>%
 filter(!is.na(max_index)) %>%
 select(-starts_with("index_"), -max_index, -nr) %>%
 distinct() %>%
 mutate(row_seq = map2(min_row, max_row, seq)) %>%
 unnest(row_seq) %>%
 select(-min_row, -max_row, -row_seq) %>%
 group_by(cust_id) %>%
 mutate(type = paste0(type, row_number())) %>%
 ungroup()
}
result = reshape(input)

&&

Leave a Reply