Merged Cells! - OmidBI

Convert the Question table with the merge headers into the result table.

📌 Challenge Details and Links
Challenge Number: 66
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube

Solving the challenge of Merged Cells! with Power Query

Power Query solution 1 for Merged Cells!, proposed by Brian Julius:

let
  S = Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]), 
  Trans = Table.TransformColumnTypes(Table.Transpose(S), {"Column2", Text.Type}), 
  Repl = Table.ReplaceValue(Trans, null, "Year", Replacer.ReplaceValue, {"Column2"}), 
  Split = Table.SplitColumn(
    Repl, 
    "Column1", 
    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), 
    {"Column1", "Column1.2"}
  ), 
  RemCol = Table.RemoveColumns(Split, {"Column1.2"}), 
  ReplVal = Table.ReplaceValue(RemCol, "Column", null, Replacer.ReplaceValue, {"Column1"}), 
  FDown = Table.FillDown(ReplVal, {"Column1"}), 
  Merge = Table.CombineColumns(
    Table.TransformColumnTypes(FDown, {{"Column2", type text}}, "en-US"), 
    {"Column1", "Column2"}, 
    Combiner.CombineTextByDelimiter("*", QuoteStyle.None), 
    "Merged"
  ), 
  Prom = Table.PromoteHeaders(Merge, [PromoteAllScalars = true]), 
  Unpiv = Table.UnpivotOtherColumns(Prom, {"Department*Year"}, "Department", "Budget"), 
  Spl = Table.SplitColumn(
    Unpiv, 
    "Department*Year", 
    Splitter.SplitTextByEachDelimiter({"*"}, QuoteStyle.Csv, false), 
    {"AC", "Year"}
  ), 
  Piv = Table.Pivot(Spl, List.Distinct(Spl[AC]), "AC", "Budget"), 
  Reord = Table.ReorderColumns(Piv, {"Department", "Year", "Actual", "Budget"})
in
  Reord

Power Query solution 2 for Merged Cells!, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Depts = Source[Column1], 
  Rows = Table.ToRows(Source), 
  Header = 
    let
      H1 = List.Accumulate(Rows{0}, {}, (s, c) => s & {c ?? List.Last(s)})
    in
      List.Zip({H1, Rows{1}}), 
  Transform = List.Transform(
    List.Skip(Rows, 2), 
    each 
      let
        D = _{0}
      in
        List.Transform(
          List.Skip(List.Zip({_, Header})), 
          each [Department = D, Year = _{1}{1}, Type = _{1}{0}, V = _{0}]
        )
  ), 
  T = Table.FromRecords(List.Combine(Transform)), 
  Pivot = Table.Pivot(T, List.Distinct(T[Type]), "Type", "V", List.Sum), 
  Sort = Table.Sort(Pivot, {"Year", each List.PositionOf(Depts, [Department])})
in
  Sorts

Power Query solution 3 for Merged Cells!, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  cab = Table.Transpose(Table.DemoteHeaders(Fonte)), 
  sub = Table.ReplaceValue(
    cab, 
    null, 
    each [Column1], 
    (a, b, c) => if Text.StartsWith(a, "Column") then null else a, 
    {"Column1"}
  ), 
  pb = Table.PromoteHeaders(Table.FillDown(sub, {"Column1"})), 
  ndin = Table.UnpivotOtherColumns(pb, {"Column2", "Department"}, "Atributo", "Valor"), 
  pv = Table.Pivot(ndin, List.Distinct(ndin[Department]), "Department", "Valor"), 
  cls = Table.Sort(pv, {{"Column2", 0}, {"Atributo", 1}}), 
  res = Table.RenameColumns(cls, {{"Column2", "Year"}, {"Atributo", "Departament"}})[
    [Departament], 
    [Year], 
    [Actual], 
    [Budget]
  ]
in
  res

Power Query solution 4 for Merged Cells!, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content] meta [Range = "B2:G8", Header = false], 
  Transpose = Table.Transpose(Source), 
  FilledDown = Table.FillDown(Transpose, {"Column1"}), 
  Promote = Table.PromoteHeaders(FilledDown, [PromoteAllScalars = true]), 
  Rename = Table.RenameColumns(Promote, {{"Department", "T"}, {"Column2", "Year"}}), 
  Unpivot = Table.UnpivotOtherColumns(Rename, {"T", "Year"}, "Department", "V"), 
  Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[T]), "T", "V"), 
  Sort = Table.Sort(Pivot, {"Year", each List.PositionOf(Source[Column1], [Department])}), 
  Return = Table.ReorderColumns(Sort, {"Department", "Year", "Actual", "Budget"})
in
  Return

Power Query solution 5 for Merged Cells!, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Head = Table.FromRows(
    Table.ToColumns(
      Table.AddColumn(
        Table.FillDown(Table.FromColumns(Table.ToRows(Table.FirstN(Source, 2))), {"Column1"}), 
        "A", 
        each ([Column1] & "-" & Text.From([Column2]) ?? [Column1])
      )[[A]]
    )
  ), 
  PH = Table.RemoveFirstN(Table.PromoteHeaders(Head & Source, [PromoteAllScalars = true]), 2), 
  Unp = Table.UnpivotOtherColumns(PH, {"Department"}, "A", "V"), 
  Split = Table.SplitColumn(Unp, "A", Splitter.SplitTextByDelimiter("-"), {"A.1", "Year"}), 
  Pivot = Table.Pivot(Split, List.Distinct(Split[A.1]), "A.1", "V"), 
  Sol = Table.Sort(
    Pivot, 
    {{"Year", Order.Ascending}, each List.PositionOf(List.Skip(Source[Column1], 2), [Department])}
  )
in
  Sol

Power Query solution 6 for Merged Cells!, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Head = Table.FromRows(
    {
      Table.AddColumn(
        Table.FillDown(Table.Transpose(Table.FirstN(Source, 2)), {"Column1"}), 
        "A", 
        each [Column1] & "-" & Text.From([Column2]) ?? [Column1]
      )[A]
    }
  ), 
  PH = Table.RemoveFirstN(Table.PromoteHeaders(Head & Source, [PromoteAllScalars = true]), 2), 
  Unp = Table.UnpivotOtherColumns(PH, {"Department"}, "A", "V"), 
  Split = Table.SplitColumn(Unp, "A", Splitter.SplitTextByDelimiter("-"), {"A.1", "Year"}), 
  Pivot = Table.Pivot(Split, List.Distinct(Split[A.1]), "A.1", "V"), 
  Sol = Table.Sort(
    Pivot, 
    {{"Year", Order.Ascending}, each List.PositionOf(List.Skip(Source[Column1], 2), [Department])}
  )
in
  Sol

Power Query solution 7 for Merged Cells!, proposed by Kris Jaganah:

let
  Source    = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content], 
  Transpose = Table.Transpose(Source), 
  Fill      = Table.FillDown(Transpose, {"Column1"}), 
  Year      = Table.TransformColumns(Fill, {"Column2", each if _ = null then "Year" else _}), 
  Titles    = Table.PromoteHeaders(Year), 
  Rename    = Table.RenameColumns(Titles, {{"Department", "D"}}), 
  Unpivot   = Table.UnpivotOtherColumns(Rename, {"Year", "D"}, "Department", "Value"), 
  Pivot     = Table.Pivot(Unpivot, List.Distinct(Unpivot[D]), "D", "Value"), 
  Position  = Table.AddColumn(Pivot, "Pos", each List.PositionOf(Source[Column1], [Department])), 
  Sort      = Table.Sort(Position, {{"Year", 0}, {"Pos", 0}}), 
  Keep      = Table.SelectColumns(Sort, {"Department", "Year", "Actual", "Budget"})
in
  Keep

Power Query solution 8 for Merged Cells!, proposed by Nelson Mwangi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  DemoteHeaders = Table.DemoteHeaders(Source), 
  Transpose = Table.Transpose(DemoteHeaders), 
  NewColumn = Table.AddColumn(
    Transpose, 
    "Custom", 
    each if Text.Contains([Column1], "Col") then null else [Column1]
  ), 
  FillDown = Table.FillDown(NewColumn, {"Custom"})[[Column1], [Column2], [Custom]], 
  UnpivotSource = Table.UnpivotOtherColumns(Source, {"Department"}, "Attribute", "Value"), 
  Filternull = Table.SelectRows(UnpivotSource, each ([Department] <> null)), 
  Merge = Table.NestedJoin(
    Filternull, 
    {"Attribute"}, 
    FillDown, 
    {"Column1"}, 
    "Table1", 
    JoinKind.LeftOuter
  ), 
  Expand = Table.ExpandTableColumn(Merge, "Table1", {"Column2", "Custom"}, {"Year", "Custom"}), 
  RemoveCols = Table.RemoveColumns(Expand, {"Attribute"}), 
  Pivot = Table.Pivot(RemoveCols, List.Distinct(RemoveCols[Custom]), "Custom", "Value", List.Sum), 
  Sort = Table.Sort(Pivot, {"Year", each List.PositionOf(Source[Department], [Department])})
in
  Sort

Power Query solution 9 for Merged Cells!, proposed by Yaroslav Drohomyretskyi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transpose1 = Table.Transpose(Source), 
  Transpose2 = Table.Transpose(
    Table.CombineColumns(
      Table.TransformColumnTypes(Table.FillDown(Transpose1, {"Column1"}), {{"Column2", type text}}), 
      {"Column1", "Column2"}, 
      Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), 
      "Merged"
    )
  ), 
  Headers = Table.PromoteHeaders(
    Table.TransformColumns(Transpose2, {{"Column1", Text.Trim, type text}}), 
    [PromoteAllScalars = true]
  ), 
  Unpivot = Table.UnpivotOtherColumns(Headers, {"Department"}, "Attribute", "Value"), 
  Result = Table.Sort(
    Table.Pivot(
      Table.SplitColumn(
        Unpivot, 
        "Attribute", 
        Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
        {"Scenario", "Year"}
      ), 
      List.Distinct(
        Table.SplitColumn(
          Unpivot, 
          "Attribute", 
          Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
          {"Scenario", "Year"}
        )[Scenario]
      ), 
      "Scenario", 
      "Value", 
      List.Sum
    ), 
    {{"Year", Order.Ascending}, each List.PositionOf(Headers[Department], [Department])}
  )
in
  Result

Power Query solution 10 for Merged Cells!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  A      = Table.FillDown(Source, {"Column1"}), 
  B      = Table.Transpose(A), 
  C      = Table.FillDown(B, {"Column1"}), 
  D      = Table.PromoteHeaders(C, [PromoteAllScalars = true]), 
  E      = Table.RenameColumns(D, {{"Department", "S"}, {"Department_1", "Year"}}), 
  F      = Table.UnpivotOtherColumns(E, {"S", "Year"}, "Attribute", "Value"), 
  G      = Table.Pivot(F, List.Distinct(F[S]), "S", "Value", List.Sum), 
  H      = Table.RenameColumns(G, {{"Attribute", "Department"}}), 
  I      = Table.ReorderColumns(H, {"Department", "Year", "Actual", "Budget"})
in
  I

Power Query solution 11 for Merged Cells!, proposed by Daniel Madhadha:

let
  Source     = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Transposed = Table.Transpose(Source), 
  Fill       = Table.FillDown(Transposed, {"Column1"}), 
  Promote    = Table.PromoteHeaders(Fill, [PromoteAllScalars = true]), 
  Rename     = Table.RenameColumns(Promote, {{"Column2", "Year"}, {"Department", "AB"}}), 
  Unpivot    = Table.UnpivotOtherColumns(Rename, {"AB", "Year"}, "Attribute", "Value"), 
  Pivot      = Table.Pivot(Unpivot, List.Distinct(Unpivot[AB]), "AB", "Value", List.Sum), 
  Reorder    = Table.ReorderColumns(Pivot, {"Attribute", "Year", "Actual", "Budget"}), 
  Sol        = Table.RenameColumns(Reorder, {{"Attribute", "Department"}})
in
  Sol

Power Query solution 12 for Merged Cells!, proposed by Peter Tholstrup:

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  transpose = Table.Transpose(Source), 
  fill = Table.FillDown(transpose, {"Column1"}), 
  promote = Table.PromoteHeaders(fill), 
  rename = Table.RenameColumns(promote, {{"Department", "AB"}, {"Column2", "Year"}}), 
  unpivot_depts = Table.UnpivotOtherColumns(rename, {"AB", "Year"}, "Department", "Value"), 
  pivot_ab = Table.Pivot(unpivot_depts, List.Distinct(unpivot_depts[AB]), "AB", "Value", List.Sum), 
  reorder = Table.ReorderColumns(pivot_ab, {"Department", "Year", "Actual", "Budget"}), 
  sort = Table.Sort(
    reorder, 
    {{"Year", Order.Ascending}, each List.PositionOf(List.Skip(Source[Column1], 2), [Department])}
  )
in
  sort

Power Query solution 13 for Merged Cells!, proposed by Arnaud Duvernois:

let
  Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
  Headers = 
    let
      tbl = Table.FillDown(
        Table.TransformColumns(Table.Transpose(Table.FirstN(Source, 2)), {}, each Text.From(_)), 
        {"Column1", "Column2"}
      )
    in
      List.Transform(List.Zip({tbl[Column1], tbl[Column2]}), each Text.Combine(_, "|")), 
  Table = Table.FromColumns(Table.ToColumns(Table.Skip(Source, 2)), Headers), 
  Unpivot = Table.UnpivotOtherColumns(Table, {"Department"}, "Attribut", "Value"), 
  SplitCol = Table.SplitColumn(
    Unpivot, 
    "Attribut", 
    Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
    {"Scenario", "Year"}
  ), 
  Pivot = Table.Pivot(SplitCol, List.Distinct(SplitCol[Scenario]), "Scenario", "Value"), 
  CustomSort = Table.Sort(
    Pivot, 
    {{"Year", Order.Ascending}, each List.PositionOf(SplitCol[Department], [Department])}
  )
in
  CustomSort

Solving the challenge of Merged Cells! with Excel

Excel solution 1 for Merged Cells!, proposed by Bo Rydobon 🇹🇭:

=LET(b,E4:G8,L,LAMBDA(x,TOCOL(IF(b,x),,1)),IFNA(HSTACK(L(B4:B8),L(E3:G3),L(C4:D8),L(b)),""))

For More fun
=LET(z,B2:G8,y,DROP(INDEX(z,2,),,1),c,DROP(TAKE(z,,1),2),
d,UNIQUE(TOCOL(c&-y,,1)),h,DROP(TAKE(z,1),,1),f,SCAN(,h,LAMBDA(a,v,IF(v>0,v,a))),u,UNIQUE(f,1),
rs,SORT(REDUCE(HSTACK(TEXTBEFORE(d,"-"),--TEXTAFTER(d,"-")),u,LAMBDA(a,v,HSTACK(a,MAP(d,LAMBDA(e,SUM((e&v=c&-y&f)*DROP(z,2,1))))))),2),
VSTACK(HSTACK(@+z,"Year",u),rs))

Excel solution 2 for Merged Cells!, proposed by 🇰🇷 Taeyong Shin:

Excel solution 3 for Merged Cells!, proposed by محمد حلمي:

=
LET(i,UNIQUE(TOCOL(B4:B8&C3:G3,,1)),IFNA(HSTACK(TEXTSPLIT(i,2),--RIGHT(i,4),TOCOL(C4:D8,,1),TOCOL(E4:G8,,1)),""))

Excel solution 4 for Merged Cells!, proposed by 🇵🇪 Ned Navarrete C.:

Excel solution 5 for Merged Cells!, proposed by Oscar Mendez Roca Farell:

=LET(
    a,
     TOROW(
         C2:G2,
          1
     ),
     u,
     UNIQUE(
         C3:G3,
          1
     ),
     d,
     B4:B8,
     WRAPCOLS(
         TOCOL(
             HSTACK(
                 REPT(
                     d,
                      u^0
                 ),
                  REPT(
                      u,
                       ROW(
                           d
                       )^0
                  ),
                  XLOOKUP(
                      d & TOROW(
                          TOCOL(
                              a
                          )&REPT(
                              u,
                              {1; 1}
                          )
                      ),
                       TOCOL(
                           d & HSTACK(
                               C2 & C3:D3,
                                E2 & E3:G3
                           )
                       ),
                       TOCOL(
                           C4:G8
                       ),
                       ""
                  )
             ),
              ,
              1
         ),
          ROWS(
                           d
                       )*COUNT(
                           u
                       )
     )
)

Excel solution 6 for Merged Cells!, proposed by Julian Poeltl:

=LET(T,B2:G8,Y,DROP(CHOOSEROWS(T,2),,1),UY,UNIQUE(TRANSPOSE(Y)),CY,COUNT(UY),D,DROP(TAKE(T,,1),2),CD,COUNTA(D),RA,XMATCH("Budget",TAKE(T,1)),R,MAKEARRAY(CY*CD,2,LAMBDA(A,B,CHOOSE(B,INDEX(D,MOD(A-1,CD)+1),INDEX(UY,ROUNDUP(A/CD,0))))),A,TOCOL(OFFSET(T,2,1,CD,RA-2),,TRUE),B,TOCOL(OFFSET(T,2,RA-1,CD,CY),,TRUE),IFERROR(VSTACK(HSTACK(INDEX(T,1,1),"Year",INDEX(T,1,2),INDEX(T,1,RA)),HSTACK(R,A,B)),""))

Excel solution 7 for Merged Cells!, proposed by Julian Poeltl:

Excel solution 8 for Merged Cells!, proposed by Asheesh Pahwa:

=LET(
    ab,
    C2:G2,
    d,
    B4:B8,
    s,
    SCAN(
        "",
        ab,
        LAMBDA(
            x,
            y,
            IF(
                y="",
                x,
                y
            )
        )
    ),    r,
    IFNA(
        DROP(
            REDUCE(
                "",
                K2:L2,
                LAMBDA(
                    a,
                    v,
                    HSTACK(
                        a,
                        LET(
                            
                            f,
                            FILTER(
                                C3:G8,
                                s=v
                            ),
                            t,
                            TAKE(
                                f,
                                1
                            ),
                            ca,
                            TOCOL(
                                d&"-"&v&"-"&t,
                                ,
                                1
                            ),
                            
                            TOCOL(
                                DROP(
                                f,
                                1
                            ),
                                ,
                                1
                            )
                        )
                    )
                )
            ),
            ,
            1
        ),
        ""
    ),
    cn,
    TOCOL(
        d&"-"&UNIQUE(
            C3:G3,
            1
        ),
        ,
        1
    ),    HSTACK(
        TEXTSPLIT(
            cn,
            "-"
        ),
        TEXTAFTER(
            cn,
            "-"
        ),
        r
    )
)

Excel solution 9 for Merged Cells!, proposed by Hussein SATOUR:

=IFNA(
    LET(
        a,
        TOCOL(
            B4:B8&"/"&SEQUENCE(
                ,
                3,
                2022
            ),
            ,
            1
        ),
        HSTACK(
            TEXTBEFORE(
                a,
                "/"
            ),
            TEXTAFTER(
                a,
                "/"
            ),
            TOCOL(
                C4:D8,
                ,
                1
            ),
            TOCOL(
                E4:G8,
                ,
                1
            )
        )
    ),
    ""
)

Excel solution 10 for Merged Cells!, proposed by Peter Bartholomew:

= SCAN(
    "",
     category,    LAMBDA(
        acc,
         cat,
         IF(
             cat<>"",
              cat,
              acc
         )
    )
)

Excel solution 11 for Merged Cells!, proposed by Pieter de Bruijn:

=LET(
    a,
    TOCOL(
        IFS(
            C4:G8,
            B4:B8&"|"&C3:G3&"|"&SCAN(
                "",
                C2:G2,
                LAMBDA(
                    a,
                    b,
                    IF(
                        b="",
                        a,
                        b
                    )
                )
            )&"|"&C4:G8
        ),
        ,
        1
    ),
    b,
    UNIQUE(
        TEXTBEFORE(
            a,
            "|",
            2
        )
    ),
    c,
    TEXTBEFORE(
        a,
        "|",
        3
    ),
    HSTACK(
        TEXTSPLIT(
            TEXTAFTER(
                "|"&b,
                "|",
                {1,
                2}
            ),
            "|"
        ),
        XLOOKUP(
            b&{"|Actual",
            "|Budget"}&"*",
            a,
            TEXTAFTER(
                a,
                "|",
                -1
            ),
            "",
            2
        )
    )
)

Solving the challenge of Merged Cells! with Python

Python solution 1 for Merged Cells!, proposed by Konrad Gryczan, PhD:

import pandas as pd

input = pd.read_excel("CH-066 Merged cells.xlsx", skiprows=1, usecols="B:G", header=None, nrows=7)
test = pd.read_excel("CH-066 Merged cells.xlsx", skiprows=1, usecols="I:L", nrows=16)
test.columns = test.columns.str.replace('.1', '')

input = input.transpose()
input[0] = input[0].fillna(method='ffill')
input.columns = input.iloc[0]
input = input[1:]
input = input.rename(columns={'Department': 'Scenario'})
input = input.rename(columns={input.columns[1]: 'Year'})

for i in range(1, len(input.columns)):
 input[input.columns[i]] = pd.to_numeric(input[input.columns[i]], errors='coerce')

input = pd.melt(input, id_vars=['Scenario', 'Year'], var_name='Department', value_name='Value')
input = input.pivot_table(index=['Department', 'Year'], columns='Scenario', values='Value').reset_index()
input = input.sort_values(['Year', 'Department']).reset_index(drop=True)
input["Budget"] = input["Budget"].astype('int64')
input.columns.name = None

test = test.sort_values(['Year', 'Department']).reset_index(drop=True)

print(input.equals(test)) # True

Solving the challenge of Merged Cells! with R

R solution 1 for Merged Cells!, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
library(unpivotr)

input = read_excel("files/CH-066 Merged cells.xlsx", range = "B2:G8", col_names = F)
test = read_excel("files/CH-066 Merged cells.xlsx", range = "I2:L17")

result = input %>% 
 as_cells() %>%
 behead("up-left", "scenario") %>%
 behead("up", "Year") %>%
 behead("left", "Department") %>%
 select(Department, Year, scenario, dbl, chr) %>%
 mutate(value = case_when(
 !is.na(dbl) ~ dbl,
 !is.na(chr) ~ as.numeric(chr),
 TRUE ~ NA_real_
 ),
 Year = as.numeric(Year)) %>%
 select(-dbl, -chr) %>%
 pivot_wider(names_from = scenario, values_from = value)

identical(result, test)
# [1] TRUE

R solution 2 for Merged Cells!, proposed by Anil Kumar Goyal:

library(tidyverse)

df <- openxlsx::read.xlsx(
 "OM Challanges/CH-066 Merged cells.xlsx",
 colNames = FALSE,
 fillMergedCells = TRUE,
 cols = 2:7,
 startRow = 2
)

df %>%
 set_names(df %>%
 slice(1:2) %>%
 summarise(across(
 everything(), ~ str_c(unique(.), collapse = "_")
 ))) %>%
 slice(-(1:2)) %>%
 pivot_longer(
 -Department,
 names_sep = "_",
 names_to = c(".value", "Year"),
 values_transform = as.numeric,
 cols_vary = "slowest"
 )

Solving the challenge of Merged Cells! with Power Query

Solving the challenge of Merged Cells! with Excel

Solving the challenge of Merged Cells! with Python

Solving the challenge of Merged Cells! with R

Leave a Reply Cancel reply