Transpose First/Last Names

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    "Column1", 
    {
      "R", 
      each Record.Combine(
        List.Combine(
          List.Transform(
            Table.Split(Table.RemoveColumns(_, "Column1"), 2), 
            each Table.ToRecords(Table.PromoteHeaders(_))
          )
        )
      )
    }
  ), 
  Expand = Table.ExpandRecordColumn(
    Group, 
    "R", 
    List.Select(
      List.Union(List.Transform(Group[R], Record.FieldNames)), 
      each Text.StartsWith(_, "V")
    )
  ), 
  Rename = Table.RenameColumns(Expand, {{"Column1", "Group"}})
in
  Rename

let
  Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]), 
  P = Table.Combine(
    List.Transform(
      List.Numbers(1, List.Count(Source) / 2, 2), 
      each 
        let
          r = Source{_}
        in
          Table.FromRows(
            List.Accumulate(
              List.Skip(List.Positions(r)), 
              {}, 
              (s, c) => if r{c} is number then s & {{r{0}, Source{_ - 1}{c}, r{c}}} else s
            ), 
            {"Group", "A", "V"}
          )
    )
  ), 
  S = Table.Pivot(P, List.Sort(List.Distinct(P[A])), "A", "V", List.Sum)
in
  S

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Replace = Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {}), 
  Transpose = Table.Transpose(Replace), 
  Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars = true]), 
  Unpivot = Table.Unpivot(
    Promote, 
    {"3", "3_7", "2_6", "2_5", "2_4", "2", "1_3", "1_2", "1", "1_1"}, 
    "Group", 
    "Value"
  ), 
  Idx = Table.AddIndexColumn(Unpivot, "Index", 1, 1, Int64.Type), 
  Idx1 = Table.AddIndexColumn(Idx, "Index.1", 0, 1, Int64.Type), 
  Merge = Table.NestedJoin(Idx1, {"Index"}, Idx1, {"Index.1"}, "Added Index1", JoinKind.LeftOuter), 
  Xpand = Table.ExpandTableColumn(Merge, "Added Index1", {"Value"}, {"Val"}), 
  Remove = Table.RemoveColumns(Xpand, {"Index", "Index.1"}), 
  Filter = Table.SelectRows(
    Remove, 
    each not Text.StartsWith(Text.From([Val]), "V") and Text.From([Val]) <> "0"
  ), 
  TrimGroup = Table.TransformColumns(
    Filter, 
    {"Group", each Number.FromText(Text.BeforeDelimiter(_, "_"))}
  ), 
  Sort = Table.Sort(TrimGroup, {{"Value", Order.Ascending}, {"Group", Order.Ascending}}), 
  Pivot = Table.Pivot(Sort, List.Distinct(Sort[Value]), "Value", "Val")
in
  Pivot

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Group = Table.Group(Source, {"Column1"}, {{"All", each Table.Combine(List.Transform(Table.Split(_,2), 
 each Table.FromColumns(List.Transform(Table.ToRows(_), List.RemoveFirstN), {"A","B"})))}}),
 Expand = Table.SelectRows(Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0})), each ([A] <> null)),
 Sol = Table.RenameColumns(Table.Pivot(Expand, List.Distinct(Expand[A]), "A", "B"), {"Column1", "Group"})
in
 Sol
y....
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Group = Table.Combine(Table.Group(Source, {"Column1"}, {{"All", (x)=> Table.AddColumn(Table.PromoteHeaders(Table.Transpose(Table.SelectRows(Table.Combine(List.Transform(Table.Split(x,2), 
 each Table.FromColumns(List.Transform(Table.ToRows(_), List.RemoveFirstN), {"A","B"}))), each [A]<>null))), "Group", each x[Column1]{0})}})[All]),
 Sol = Table.ReorderColumns(Group, {List.Last(Table.ColumnNames(Group))}&List.RemoveLastN(Table.ColumnNames(Group)))
in
 Sol
                    
                  
          

let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Group = Table.Sort(
    Table.Combine(
      Table.Group(
        Source, 
        {"Column1"}, 
        {
          {
            "All", 
            each 
              let
                a = Table.Split(_, 2), 
                b = List.Transform(
                  a, 
                  each 
                    let
                      c = Table.PromoteHeaders(_), 
                      d = Table.RenameColumns(c, {Table.ColumnNames(c){0}, "Group"}), 
                      e = Table.UnpivotOtherColumns(d, {"Group"}, "A", "B")
                    in
                      e
                ), 
                f = Table.Combine(b)
              in
                f
          }
        }
      )[All]
    ), 
    "A"
  ), 
  Sol = Table.Pivot(Group, List.Distinct(Group[A]), "A", "B")
in
  Sol

let
  Fonte = Tabela1, 
  gp = Table.Group(
    Fonte, 
    {"Column1"}, 
    {
      {
        "Contagem", 
        each [
          a = List.Combine(List.Split(Table.ToRows(_), 2)), 
          b = List.Zip(
            {List.Combine(List.Alternate(a, 1, 1, 1)), List.Combine(List.Alternate(a, 1, 1))}
          ), 
          c = Table.PromoteHeaders(
            Table.Transpose(Table.SelectRows(Table.FromRows(b), each [Column1] is text))
          )
        ][c]
      }
    }
  ), 
  res = Table.ExpandTableColumn(gp, "Contagem", Table.ColumnNames(gp[Contagem]{0}))
in
  res

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = List.Combine(List.Transform(Table.Split(Origen, 2), each Record.FieldValues(_{0}))), 
  b = List.Combine(List.Transform(Table.Split(Origen, 2), each Record.FieldValues(_{1}))), 
  c = Table.AddColumn(
    Table.FromColumns({a, b}), 
    "Group", 
    each if [Column1] is number then [Column1] else null
  ), 
  d = Table.SelectRows(Table.FillDown(c, {"Group"}), each [Column1] is text), 
  Sol = Table.Pivot(d, List.Distinct(d[Column1]), "Column1", "Column2")
in
  Sol

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = Table.Transpose(Origen), 
  b = List.Combine(
    Table.ToColumns(Table.SelectColumns(a, List.Alternate(Table.ColumnNames(a), 1, 1, 1)))
  ), 
  c = List.Combine(
    Table.ToColumns(Table.SelectColumns(a, List.Alternate(Table.ColumnNames(a), 1, 1)))
  ), 
  d = Table.AddColumn(
    Table.FromColumns({b, c}), 
    "Group", 
    each if [Column1] is number then [Column1] else null
  ), 
  e = Table.SelectRows(Table.FillDown(d, {"Group"}), each [Column1] is text), 
  Sol = Table.Pivot(e, List.Distinct(e[Column1]), "Column1", "Column2")
in
  Sol

let
 Source = Excel.CurrentWorkbook(){[Name="tData131"]}[Content],
 Rename = Table.RenameColumns(Source, {"Column1", "Group"}),
 Group = Table.Group(Rename, {"Group"}, {"All", each let 
 _L = List.Split(List.Combine(List.Skip(Table.ToColumns(_))),2),
 _RN = List.Combine(List.Select(_L, each _{0}<>null)),
 _in Record.FromList(List.Alternate(_RN,1,1), List.Alternate(_RN,1,1,1)) }),
 FName = List.Union(List.Transform(Group[All], Record.FieldNames)), 
 Expand = Table.ExpandRecordColumn(Group, "All", List.Sort(FName))
in
 Expand
                    
                  
          

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  C = Table.ToRows(Source), 
  C2 = Table.FromColumns({List.Alternate(C, 1, 1, 1), List.Alternate(C, 1, 1)}), 
  A = Table.AddColumn(C2, "Group", each Table.FromColumns({[Column1], [Column2]})), 
  R = Table.SelectColumns(A, {"Group"}), 
  E = Table.ExpandTableColumn(R, "Group", {"Column1", "Column2"}, {"Column1", "Column2"}), 
  F = Table.SelectRows(E, each ([Column1] <> null)), 
  C3 = Table.TransformColumnTypes(F, {{"Column1", type text}}), 
  A2 = Table.AddColumn(
    C3, 
    "Group", 
    each if Text.Contains([Column1], "Value") then null else [Column1]
  ), 
  F2 = Table.FillDown(A2, {"Group"}), 
  F3 = Table.SelectRows(F2, each Text.Contains([Column1], "Value")), 
  P = Table.Pivot(F3, List.Distinct(F3[Column1]), "Column1", "Column2")
in
  P

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TableSplit = Table.Split(Source, 2), 
  ListTransform = List.Transform(TableSplit, each Table.PromoteHeaders(_)), 
  RenameColumns = List.Transform(
    ListTransform, 
    each Table.RenameColumns(_, {Table.ColumnNames(_){0}, "Group"})
  ), 
  UnpivotOtherColumnsThanGroup = List.Transform(
    RenameColumns, 
    each Table.UnpivotOtherColumns(_, {"Group"}, "Attribute", "Value")
  ), 
  CombineTables = Table.Combine(UnpivotOtherColumnsThanGroup), 
  SortedRows = Table.Sort(CombineTables, {{"Attribute", Order.Ascending}}), 
  PivotedColumn = Table.Pivot(
    SortedRows, 
    List.Distinct(SortedRows[Attribute]), 
    "Attribute", 
    "Value", 
    List.Sum
  )
in
  PivotedColumn

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Table.Split(Source, 2), 
  Custom3 = List.Transform(
    Custom1, 
    each Table.UnpivotOtherColumns(
      Table.PromoteHeaders(_), 
      {Text.From(Table.FirstValue(_))}, 
      "Attribute", 
      "Value"
    )
  ), 
  Custom2 = List.Transform(Custom3, each Table.RenameColumns(_, {Table.ColumnNames(_){0}, "Group"})), 
  Custom4 = Table.Combine(Custom2), 
  #"Sorted Rows" = Table.Sort(Custom4, {{"Attribute", Order.Ascending}}), 
  #"Pivoted Column" = Table.Pivot(
    #"Sorted Rows", 
    List.Distinct(#"Sorted Rows"[Attribute]), 
    "Attribute", 
    "Value", 
    List.Sum
  )
in
  #"Pivoted Column"

lete the empty column)

Happy Learning 🎉



                    
                  
          

=LET(a,A1:A10,d,B1:F10,w,WRAPROWS(TOCOL(IF(d>"",a&d,d),,1),2),h,UNIQUE(TOROW(IFS(d>"",d),3),1),n,UNIQUE(a),g,VLOOKUP(n&h,w,2,0),HSTACK(VSTACK("Group",n),VSTACK(h,IFNA(IF(g,g,""),""))))

=LET(i,A1:A10,b,B1:F10,s,FIND("V",b),x,REDUCE(HSTACK("Group",UNIQUE(TOROW(IF(s,b),2),1)),UNIQUE(i),LAMBDA(a,v,IFNA(VSTACK(a,HSTACK(v,TOROW(IFS(i=v,s*B2:F11),2))),""))),IF(x=0,"",x))

=DROP(REDUCE(0,UNIQUE(A1:A10),LAMBDA(a,d,LET(x,SORT(WRAPCOLS(TOCOL(FILTER(B1:F10,A1:A10=d),,1),2),,,1),c,HSTACK(d,DROP(FILTER(x,TAKE(x,1)>0),1)),IFNA(VSTACK(a,IF(c,c,"")),"")))),1)

=LET(u,A1:A10,b,B1:F10,i,TOCOL(b),REDUCE(HSTACK("Group",TOROW(UNIQUE(FILTER(i,i>"")))),UNIQUE(u),LAMBDA(a,d,LET(x,SORT(WRAPCOLS(TOCOL(FILTER(b,u=d),,1),2),,,1),c,DROP(FILTER(x,TAKE(x,1)>0),1),IFNA(VSTACK(a,HSTACK(d,IF(c,c,""))),"")))))

=LET(d,B1:F10,F,LAMBDA(x,TOCOL(IFS(d>"",x),2)),pv,PIVOTBY(F(A1:A10),F(d),F(DROP(d,1)),SUM,,0,,0),IF(pv>0,pv,""))

=LET(a,A1:A10,b,B1:F10,c,TOCOL(a&"-"&b,,1),d,VSTACK(DROP(c,1),0),e,UNIQUE(a),f,TOROW(b),g,UNIQUE(FILTER(f,LEFT(f)="V"),1),HSTACK(VSTACK("Group",e),VSTACK(g,IFERROR(XLOOKUP(e&"-"&g,c,--TEXTAFTER(d,"-")),""))))

=LET(a,B2:F10,f,LAMBDA(x,TOCOL(IFS(a,x),3)),b,PIVOTBY(f(A2:A10),f(B1:F9),f(a),SUM,,0,,0),IF(TAKE(b,1)&TAKE(b,,1)="","Group",b))

=LET(
 _s, SEARCH("val", B1:F10),
 VSTACK(
 HSTACK("Group", UNIQUE(TOROW(IF(_s, B1:F10), 3), 1)),
 IFNA(
 DROP(
 REDUCE(
 "🌻Thank you Vijay sir🌼",
 UNIQUE(A1:A10),
 LAMBDA(a, v, VSTACK(a, HSTACK(v, TOROW(FILTER(IF(_s, B2:F11), A1:A10 = v), 3))))
 ),
 1
 ),
 ""
 )
 )
)

=LET(t,A1:F10,n,INDEX(t,,1),d,DROP(t,,1),F,LAMBDA(x,y,CHOOSEROWS(x,SEQUENCE(ROWS(t)/2,,y,2))),c,TOCOL(IF(F(d,1)<>"",F(n,1))), PIVOTBY(c,TOCOL(F(d,1)),TOCOL(F(d,2)),SUM,0,0,,0,,c))

library(tidyverse)
library(readxl)
input = read_excel("PQ_Challenge_131.xlsx", range = "A1:F10", col_names = FALSE)
test = read_excel("PQ_Challenge_131.xlsx", range = "I1:R4", col_names = TRUE)
labels = input %>% 
 filter(row_number() %% 2 == 1) %>%
 pivot_longer(cols = -c(...1), names_to = "row", values_to = "label")
values = input %>% 
 filter(row_number() %% 2 == 0) %>%
 pivot_longer(cols = -c(...1), nam&es_to = "row", values_to = "value")
final = bind_cols(labels, values) %>% 
 select(Group = ...1, label , value ) %>%
 filter(!is.na(label)) %>%
 pivot_wider(names_from = label, values_from = value) %>%
 mutate(across(everything(), ~ifelse(is.na(.), NA_integer_, as.numeric(.))))