Table Transformation! Part 13

Transform the question structure into the result structure.

📌 Challenge Details and Links
Challenge Number: 131
Challenge Difficulty: ⭐⭐
Designed by: Iván Cortinas Rodríguez
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube

Solving the challenge of Table Transformation! Part 13 with Power Query

Power Query solution 1 for Table Transformation! Part 13, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  T = each Table.FromList(
    Table.ToRows(Source), 
    (r) => _(r), 
    List.FirstN(Table.ColumnNames(Source), 4)
  ), 
  S = T(each List.FirstN(_, 4)) & T(each {_{0}} & List.LastN(_, 3))
in
  S

Power Query solution 2 for Table Transformation! Part 13, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  T = each List.Alternate(
    List.TransformMany(Table.ToRows(Source), each List.Split(List.Skip(_), 3), (i, _) => {i{0}} & _), 
    1, 
    1, 
    _
  ), 
  S = Table.FromRows(T(1) & T(0), List.FirstN(Table.ColumnNames(Source), 4))
in
  S

Power Query solution 3 for Table Transformation! Part 13, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  H = Table.ColumnNames(Source), 
  T = each Table.SelectColumns(Source, _), 
  S = T(List.FirstN(H, 4))
    & Table.RenameColumns(
      T({H{0}} & List.LastN(H, 3)), 
      List.Zip(List.Split(List.FirstN(List.Reverse(H), 6), 3))
    )
in
  S

Power Query solution 4 for Table Transformation! Part 13, proposed by Brian Julius:

let
 S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 ToCols = Table.ToColumns(S),
 Rshp = (x as list) => [
 a = List.Select(ToCols, each List.Contains(x, List.PositionOf(ToCols, _))),
 b = List.Transform(a, each List.Skip(_, 1)),
 c = Table.FromColumns(b, List.FirstN(Table.ColumnNames(Table.PromoteHeaders(S)), 4))
 ][c],
 App = Rshp({0, 1, 2, 3}) & Rshp({0, 4, 5, 6})
in
 App

NOTE: table loaded initially w/o headers

Power Query solution 5 for Table Transformation! Part 13, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  res = 
    let
      a = Table.ToRows(Fonte), 
      b = List.Zip(List.Transform(a, each List.Split(List.Skip(_), 3))), 
      c = List.Combine(b), 
      d = List.Repeat(Fonte[ID], List.Count(c) / List.Count(Fonte[ID]))
    in
      Table.FromRows(
        List.Transform({0 .. List.Count(c) - 1}, (x) => {d{x}} & c{x}), 
        List.FirstN(Table.ColumnNames(Fonte), 4)
      )
in
  res

Power Query solution 6 for Table Transformation! Part 13, proposed by Ramiro Ayala Chávez:

let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = S[[ID],[Quantity],[Payment],[Discount]],
b = S[[ID],[Quantity2],[Payment3],[Discount4]],
c = List.Zip({Table.ColumnNames(b),Table.ColumnNames(a)}),
Sol = a&Table.RenameColumns(b,c)
in
Sol

Power Query solution 7 for Table Transformation! Part 13, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content] meta [Table = "C2:I7", Header = false], 
  ToCols = Table.ToColumns(Source), 
  Split = List.Split(List.Skip(ToCols), 3), 
  Transform = List.Transform(
    Split, 
    each [C = ({ToCols{0}} & _), T = Table.FromColumns(C), R = Table.PromoteHeaders(T)][R]
  ), 
  Return = Table.Combine(Transform)
in
  Return

Power Query solution 8 for Table Transformation! Part 13, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  List1 = Table.ToColumns(Source), 
  Div = (List.Count(List1) - 1) / 2, 
  Split = List.Split(List.Skip(List1), Div), 
  List2 = {0 .. Div - 1}, 
  Sol = Table.FromColumns(
    {List1{0} & List1{0}} & List.Transform(List2, each Split{0}{_} & Split{1}{_}), 
    List.FirstN(Table.ColumnNames(Source), 4)
  )
in
  Sol

Power Query solution 9 for Table Transformation! Part 13, proposed by Kris Jaganah:

let
  A = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  B = Table.Transpose(A), 
  C = Table.PromoteHeaders(B), 
  D = Table.Combine(Table.Group(C, {"ID"}, {"All", each Table.AddIndexColumn(_, "Idx")})[All]), 
  E = Table.UnpivotOtherColumns(D, {"ID", "Idx"}, "Id", "V"), 
  F = Table.Pivot(E, List.Distinct(E[ID]), "ID", "V", List.Sum), 
  G = Table.RemoveColumns(F, {"Idx"})
in
  G

Power Query solution 10 for Table Transformation! Part 13, proposed by Abdallah Ally:

let
  Source    = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ToCols    = Table.ToColumns(Source), 
  Cols      = List.FirstN(Table.ColumnNames(Source), 4), 
  Split     = List.Split(List.Skip(ToCols), 3), 
  Transform = List.Transform(Split, each Table.FromColumns({ToCols{0}} & _, Cols)), 
  Result    = Table.Combine(Transform)
in
  Result

Power Query solution 11 for Table Transformation! Part 13, proposed by Yaroslav Drohomyretskyi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  result = Table.SelectColumns(Source, {"ID", "Quantity", "Payment", "Discount"})
    & Table.TransformColumnNames(
      Table.RemoveColumns(Source, {"Quantity", "Payment", "Discount"}), 
      each Text.Remove(_, {"0" .. "9"})
    )
in
  result

Power Query solution 12 for Table Transformation! Part 13, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A = Table.AddColumn(
    S, 
    "T", 
    each Table.AddIndexColumn(
      Table.FromColumns(
        List.Zip(List.Split(List.Skip(Record.ToList(_), 1), 3)), 
        {"Quantity", "Payment", "Discount"}
      ), 
      "Ind", 
      1, 
      1
    )
  ), 
  B = Table.SelectColumns(A, {"ID", "T"}), 
  C = Table.ExpandTableColumn(
    B, 
    "T", 
    {"Quantity", "Payment", "Discount", "Ind"}, 
    {"Quantity", "Payment", "Discount", "Ind"}
  ), 
  D = Table.Sort(C, {{"Ind", Order.Ascending}, {"ID", Order.Ascending}}), 
  E = Table.RemoveColumns(D, {"Ind"})
in
  E

Power Query solution 13 for Table Transformation! Part 13, proposed by Ahmed Ariem:

let
f= (x) => {{x{0}, x{1}, x{2}, x{3}}} & 
 {{x{0}, x{4}, x{5}, x{6}}},
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
to = Table.FromRows(
 List.Combine(Table.ToList(Source, f)),
 {"ID", "Quantity", "Payment","Discount"}
 )
in
 to

Power Query solution 14 for Table Transformation! Part 13, proposed by Sanket Doijode:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Duplicate = Table.DuplicateColumn(Source, "ID", "ID - Copy"), 
  Expand = Table.ExpandTableColumn(
    Table.FromList(
      List.Transform(
        List.Split(
          Table.ToColumns(
            Table.ReorderColumns(
              Duplicate, 
              {
                "ID", 
                "Quantity", 
                "Payment", 
                "Discount", 
                "ID - Copy", 
                "Quantity2", 
                "Payment3", 
                "Discount4"
              }
            )
          ), 
          4
        ), 
        each Table.FromColumns(_)
      ), 
      Splitter.SplitByNothing()
    ), 
    "Column1", 
    {"Column1", "Column2", "Column3", "Column4"}, 
    {"ID", "Quantity", "Payment", "Discount"}
  )
in
  Expand

Solving the challenge of Table Transformation! Part 13 with Excel

Excel solution 1 for Table Transformation! Part 13, proposed by 🇰🇷 Taeyong Shin:

=WRAPCOLS(
    TOCOL(
        HSTACK(
            C3:C7&{"",
            ""},
            SORTBY(
                D3:I7,
                D2:I2,
                -1
            )
        ),
        ,
        1
    ),
    ROWS(
        D3:I7
    )*2
)

Excel solution 2 for Table Transformation! Part 13, proposed by Oscar Mendez Roca Farell:

Excel solution 3 for Table Transformation! Part 13, proposed by Julian Poeltl:

=LET(
    A,
    D3:I7,
    U,
    UNIQUE(
        D2:I2,
        1
    ),
    I,
    C3:C7,
    C,
    COLUMNS(
        U
    ),
    S,
    INDEX(
        I,
        MOD(
            SEQUENCE(
                COLUMNS(
                    A
                )*ROWS(
                    A
                )/C
            )-1,
            ROWS(
                I
            )
        )+1
    ),
    VSTACK(
        HSTACK(
            "ID",
            U
        ),
        HSTACK(
            S,
            WRAPROWS(
                TOCOL(
                    A
                ),
                C
            )
        )
    )
)

Excel solution 4 for Table Transformation! Part 13, proposed by Sunny Baggu:

=VSTACK(     C2:F2,     HSTACK(          TOCOL(
              IF(
                  {1,
                   2},
                   C3:C7
              ),
               ,
               1
          ),          VSTACK(
              
               CHOOSECOLS(
                   D3:I7,
                    XMATCH(
                        D2:F2,
                         D2:I2
                    )
               ),
              
               CHOOSECOLS(
                   D3:I7,
                    XMATCH(
                        D2:F2,
                         D2:I2,
                         ,
                         -1
                    )
               )
               
          )     ))

Excel solution 5 for Table Transformation! Part 13, proposed by Andy Heybruch:

=HSTACK(
    VSTACK(
        C2:C7,
        C3:C7
    ),
    VSTACK(
        D2:F7,
        G3:I7
    )
)

Excel solution 6 for Table Transformation! Part 13, proposed by Asheesh Pahwa:

Excel solution 7 for Table Transformation! Part 13, proposed by Bilal Mahmoud kh.:

=HSTACK(
    VSTACK(
        C2:C7,
        C3:C7
    ),
    VSTACK(
        D2:F2,
        TAKE(
            D3:I7,
            5,
            3
        ),
        TAKE(
            D3:I7,
            5,
            -3
        )
    )
)

Excel solution 8 for Table Transformation! Part 13, proposed by Bilal Mahmoud kh.:

Excel solution 9 for Table Transformation! Part 13, proposed by Gerson Pineda:

=VSTACK(
    C3:F7,
    HSTACK(
        C3:C7,
        G3:I7
    )
)

=VSTACK(
    TAKE(
        C3:I7,
        ,
        4
    ),
    CHOOSECOLS(
        C3:I7,
        1,
        5,
        6,
        7
    )
)

Excel solution 10 for Table Transformation! Part 13, proposed by Hamidi Hamid:

=LET(
    z,
    DROP(
        WRAPCOLS(
            TOCOL(
                SORTBY(
                    D2:I7,
                    D2:I2,
                    -1
                )
            ),
            6
        ),
        ,
        1
    ),
    VSTACK(
        UNIQUE(
            C2:I2,
            1
        ),
        HSTACK(
            VSTACK(
                C3:C7,
                C3:C7
            ),
            WRAPCOLS(
                TOCOL(
                    z
                ),
                ROWS(
                    C3:C7
                )*2,
                
            )
        )
    )
)

Excel solution 11 for Table Transformation! Part 13, proposed by Muhammad Jabir:

=LET(a,C2:F7,b,C2:I7,r,ROWS(C2:I7),VSTACK(a,DROP(INDEX(b,SEQUENCE(r),{1,5,6,7}),1)))

Excel solution 12 for Table Transformation! Part 13, proposed by Peter Bartholomew:

= LET(     first,
     TAKE(
         data,
         ,
         3
     ),     final,
     TAKE(
         data,
         ,
         -3
     ),     VSTACK(
         first,
          final
     ))

Excel solution 13 for Table Transformation! Part 13, proposed by Rick Rothstein:

=LET(g,
    C2:I7,
    r,
    ROWS(
        g
    ),
    c,
    (COLUMNS(
        g
    )-1)/2,
    k,
    TAKE,
    VSTACK(
        k(
            g,
            ,
            c+1
        ),
        HSTACK(
            k(
                g,
                1-r,
                1
            ),
            k(
                g,
                1-r,
                -c
            )
        )
    ))

Solving the challenge of Table Transformation! Part 13 with Python

Python solution 1 for Table Transformation! Part 13, proposed by Konrad Gryczan, PhD:

import pandas as pd

path = "CH-131 Table Transformation.xlsx"
input = pd.read_excel(path, usecols="C:I", skiprows=1, nrows=5).rename(columns=lambda x: x.replace('.1', ''))
test = pd.read_excel(path, usecols="K:N", skiprows=1, nrows=11).rename(columns=lambda x: x.replace('.1', '').replace('.2', ''))

result = pd.concat([input.iloc[:, :4], input.iloc[:, [0, 4, 5, 6]]], axis=0).reset_index(drop=True)

print(result.equals(test))  # True

Python solution 2 for Table Transformation! Part 13, proposed by Luan Rodrigues:

import pandas as pd

file = "CH-131 Table Transformation.xlsx"

df = pd.read_excel(file, usecols='C:I',skiprows=1)
col = len(df.columns)
df1 = df.iloc[:,:4]
df2 = pd.concat([df.iloc[:,:1],  df.iloc[:,4:col] ] ,axis=1)
df2.columns = df1.columns
df_res = pd.concat([df1,df2]).dropna() 
 
print(df_res)

Solving the challenge of Table Transformation! Part 13 with Python in Excel

Python in Excel solution 1 for Table Transformation! Part 13, proposed by Alejandro Campos:

df = pd.concat([xl("B2:H7", headers=True)
 .rename(columns=lambda x: x.rstrip("12"))
 .iloc[:, :4],
 xl("B2:H7", headers=True)
 .rename(columns=lambda x: x.rstrip("12"))
 .iloc[:, [0, 4, 5, 6]]]).reset_index(drop=True)

Solving the challenge of Table Transformation! Part 13 with R

R solution 1 for Table Transformation! Part 13, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)

path = "files/CH-131 Table Transformation.xlsx"
input = read_excel(path, range = "C2:I7")
test = read_excel(path, range = "K2:N12")

result = bind_rows(input[,1:4], input[,c(1,5:7)]) 

all.equal(result, test)
#> [1] TRUE

Solving the challenge of Table Transformation! Part 13 with Google Sheets

Google Sheets solution 1 for Table Transformation! Part 13, proposed by Peter Krkos:

PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1352674039#gid=1352674039