Pivot Table by Agent

Pivot the given table as shown.

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

Solving the challenge of Pivot Table by Agent with Power Query

Power Query solution 1 for Pivot Table by Agent, proposed by Zoran Milokanović:

let
 Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
 S = Table.Combine(Table.Group(Table.Group(Table.FromRecords(Table.TransformRows(Source, each _ & [Year = Date.Year([Date]), Quarter = Date.QuarterOfYear([Date]), Month = DateTime.ToText([Date], "MMM", "en-US")])), {"Year", "Quarter", "Month"}, {{"Total Sale", each List.Sum([Sale])}}), {"Year"}, {{"A", each let g = List.Sum([Total Sale]), t = Table.AddColumn(_ & hashtag#table({"Year", "Total Sale"}, {{Text.From([Year]{0}) & " Total", g}}), "Sale %", each [Total Sale] / g), c = Table.RowCount(t) in Table.FromRecords(List.Generate(() => 0, each _ < c, each _ + 1, each if _ = 0 or _ = c - 1 then t{_} else Record.TransformFields(t{_}, {{"Year", each null}, {"Quarter", (r) => {r}{Number.From(t{_ - 1}[Quarter] = t{_}[Quarter])}?}})))}})[A])
in
 S

Power Query solution 2 for Pivot Table by Agent, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Year = Table.AddColumn(Source, "Year", each Date.Year([Date])), 
  Quarter = Table.AddColumn(Year, "Quarter", each Date.QuarterOfYear([Date])), 
  Month = Table.AddColumn(Quarter, "Month", each Text.Start(Date.MonthName([Date]), 3)), 
  Total = Table.Group(Month, {"Year", "Quarter", "Month"}, {{"Total Sale", each List.Sum([Sale])}}), 
  A = Table.ToColumns(Total), 
  YQ = List.Transform(
    {0, 1}, 
    (x) => {A{x}{0}}
      & List.Skip(
        List.Transform(
          {0 .. List.Count(A{x}) - 1}, 
          each if A{x}{_} = A{x}{_ - 1} then null else A{x}{_}
        )
      )
  ), 
  Total2 = Table.FromColumns(YQ & List.Skip(Table.ToColumns(Total), 2), Table.ColumnNames(Total)), 
  Sol = Table.Combine(
    Table.Group(
      Total2, 
      {"Year"}, 
      {
        {
          "B", 
          each 
            let
              a = List.Sum([Total Sale]), 
              b = Table.AddColumn(_, "Sales %", each Number.ToText([Total Sale] / a, "p0")), 
              c = Table.FromRows(
                Table.ToRows(b) & {{"Total " & Text.From(b[Year]{0}), null, null, a, "100%"}}, 
                Table.ColumnNames(b)
              )
            in
              c
        }
      }, 
      0, 
      (x, y) => Number.From(y[Year] <> null)
    )[B]
  )
in
  Sol

Power Query solution 3 for Pivot Table by Agent, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Year = Table.AddColumn(Source, "Year", each Date.Year([Date])), 
  Quarter = Table.AddColumn(Year, "Quarter", each Date.QuarterOfYear([Date])), 
  Month = Table.AddColumn(Quarter, "Month", each Text.Start(Date.MonthName([Date]), 3)), 
  Group = Table.Group(Month, {"Year", "Quarter", "Month"}, {{"Total Sale", each List.Sum([Sale])}}), 
  Sol = Table.Combine(
    Table.Group(
      Group, 
      {"Year"}, 
      {
        {
          "B", 
          each 
            let
              a = List.Sum([Total Sale]), 
              b = Table.AddColumn(_, "Sales %", each Number.ToText([Total Sale] / a, "p0")), 
              c = Table.Group(
                b, 
                {"Year", "Quarter"}, 
                {
                  "C", 
                  each Table.FromRows(
                    List.Zip({[Year], {[Quarter]{0}}, [Month], [Total Sale], [#"Sales %"]}), 
                    Table.ColumnNames(b)
                  )
                }
              ), 
              d = Table.Combine(
                Table.Group(
                  Table.Combine(c[C]), 
                  {"Year"}, 
                  {
                    "D", 
                    each Table.FromRows(
                      List.Zip({{[Year]{0}}, [Quarter], [Month], [Total Sale], [#"Sales %"]})
                    )
                  }
                )[D]
              ), 
              e = Table.FromRows(
                Table.ToRows(d) & {{"Total " & Text.From(b[Year]{0}), null, null, a, "100%"}}, 
                Table.ColumnNames(b)
              )
            in
              e
        }
      }
    )[B]
  )
in
  Sol

Power Query solution 4 for Pivot Table by Agent, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  add = List.Accumulate(
    {"Year", "Quarter", "Month"}, 
    Fonte, 
    (s, c) =>
      Table.AddColumn(
        s, 
        c, 
        each 
          if c = "Year" then
            Date.Year([Date])
          else if c = "Quarter" then
            Date.QuarterOfYear([Date])
          else
            DateTime.ToText([Date], "MMM", "en-us")
      )
  ), 
  gp = Table.Group(add, {"Year", "Quarter", "Month"}, {{"Total Sales", each List.Sum([Sale])}}), 
  grp = Table.Group(
    gp, 
    {"Year"}, 
    {
      "tab", 
      each 
        let
          a = _, 
          b = a
            & #table(
              {"Year", "Quarter", "Month", "Total Sales"}, 
              {{"Total " & Text.From(_[Year]{0}), null, null, List.Sum([Total Sales])}}
            ), 
          c = Table.AddColumn(
            b, 
            "Sales %", 
            each [Total Sales] / List.Sum(a[Total Sales]), 
            Percentage.Type
          )
        in
          c
    }
  )[tab], 
  sb = Table.ReplaceValue(
    Table.Combine(grp), 
    null, 
    each [Month], 
    (a, b, c) => if c = "Jan" or c = null then a else b, 
    {"Year"}
  ), 
  res = Table.ReplaceValue(
    sb, 
    null, 
    each [Month], 
    (a, b, c) => if c = "Jan" or c = "Apr" or c = "Jul" or c = "Oct" then a else b, 
    {"Quarter"}
  )
in
  res

Power Query solution 5 for Pivot Table by Agent, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData173"]}[Content], 
  ChangeType = Table.TransformColumnTypes(Source, {{"Date", type date}}), 
  Add_ListYQM = Table.AddColumn(
    ChangeType, 
    "L", 
    each {Date.Year([Date]), Date.QuarterOfYear([Date]), Date.ToText([Date], "MMM", "en-US")}
  ), 
  GroupYQM = Table.Group(Add_ListYQM, {"L"}, {{"Y", each [L]{0}{0}}, {"S", each List.Sum([Sale])}}), 
  GroupY = Table.Group(
    GroupYQM, 
    "Y", 
    {
      "G", 
      each 
        let
          TYQM = List.Accumulate(
            List.Skip([L]), 
            [r = {[L]{0}}, pq = [L]{0}{1}], 
            (s, c) => [r = s[r] & {{null, if (c{1} = s[pq]) then null else c{1}, c{2}}}, pq = c{1}]
          )[r], 
          SSY = List.Sum([S]), 
          TS = List.Transform([S], each {_, _ / SSY}), 
          TYRow = {Text.Combine({Text.From([Y]{0}), " Total"}), null, null, SSY, 1}, 
          Rows = List.Transform(List.Zip({TYQM, TS}), List.Combine) & {TYRow}
        in
          Table.FromRows(Rows, {"Year", "Quarter", "Month", "Total Sales", "Sales %"})
    }
  ), 
  Combine = Table.Combine(GroupY[G])
in
  Combine

Power Query solution 6 for Pivot Table by Agent, proposed by Yaroslav Drohomyretskyi:

let
  Z = (G, y) =>
    let
      F = Table.SelectRows(G, each ([Y] = y)), 
      S = Table.AddColumn(F, "Sale %", each [Total Sale] / List.Sum(F[Total Sale]), Percentage.Type), 
      T = Table.FromRecords(
        Table.ToRecords(S)
          & {
            [
              Y          = Text.From(List.Average(S[Y])) & " Total", 
              Q          = null, 
              Month      = null, 
              Total Sale = List.Sum(S[Total Sale]), 
              #"Sale %"  = List.Sum(S[#"Sale %"])
            ]
          }
      ), 
      I = Table.AddIndexColumn(T, "Index", 1, 1), 
      Y = Table.AddColumn(I, "Year", each if [Index] = 1 or [Index] = 13 then [Y] else null), 
      Q = Table.AddColumn(
        Y, 
        "Quarter", 
        each if List.Contains({1, 4, 7, 10}, [Index]) then [Q] else null
      ), 
      R = Table.SelectColumns(Q, {"Year", "Quarter", "Month", "Total Sale", "Sale %"})
    in
      R, 
  Source = Excel.CurrentWorkbook(){[Name = "T"]}[Content], 
  G = Table.Group(
    Table.AddColumn(
      Table.AddColumn(
        Table.AddColumn(Source, "Month", each Text.Start(Date.MonthName([Date], "en-US"), 3)), 
        "Q", 
        each Date.QuarterOfYear([Date])
      ), 
      "Y", 
      each Date.Year([Date])
    ), 
    {"Y", "Q", "Month"}, 
    {{"Total Sale", each List.Sum([Sale])}}
  ), 
  N = Table.AddColumn(Table.Distinct(Table.SelectColumns(G, {"Y"})), "X", each Z(G, [Y])), 
  R = Table.TransformColumnTypes(
    Table.RemoveColumns(
      Table.ExpandTableColumn(
        N, 
        "X", 
        {"Year", "Quarter", "Month", "Total Sale", "Sale %"}, 
        {"Year", "Quarter", "Month", "Total Sale", "Sale %"}
      ), 
      {"Y"}
    ), 
    {{"Sale %", Percentage.Type}}
  )
in
  R

Solving the challenge of Pivot Table by Agent with Excel

Excel solution 1 for Pivot Table by Agent, proposed by Bo Rydobon 🇹🇭:

=LET(d,A2:A731,y,YEAR(d),m,MONTH(d),g,REDUCE(D1:H1,UNIQUE(y),LAMBDA(a,v,VSTACK(a,
DROP(GROUPBY(HSTACK(m,y,ROUNDUP(m/3,),TEXT(d,"mmm")),B2:B731,HSTACK(SUM,PERCENTOF),,,,y=v),1,1)))),
c,{1,1,0,0,0},h,DROP(VSTACK(c,g),-1),IF((g="")*(N(h)>2000),h&" Total",IF((g=h)*c,"",g)))

Excel solution 2 for Pivot Table by Agent, proposed by محمد حلمي:

=LET(j,A2:A731,y,YEAR(j),m,MONTH(j),s,SEQUENCE(12),
c,(s-1)/3,q,INT(c)+1,
REDUCE(D1:H1,UNIQUE(y),LAMBDA(a,d,
IFNA(VSTACK(a,LET(
s,MAP(s,LAMBDA(a,SUM(B2:B731*(y=d)*(m=a)))),v,SUM(s),
VSTACK(HSTACK(d,IF(q=c+1,q,""),
TEXT(29*UNIQUE(m),"mmm"),s,s/v),
HSTACK(d&" Total","","",v,1)))),""))))

Excel solution 3 for Pivot Table by Agent, proposed by Julian Poeltl:

=VSTACK({"Year"."Quarter"."Month"."Total Sale"."Sale %"},LET(T,A1:B731,TT,DROP(T,1),D,CHOOSECOLS(TT,1),M,MONTH(D),Y,YEAR(D),S,CHOOSECOLS(TT,2),YU,UNIQUE(Y),MAKEARRAY(13*COUNTA(YU),5,LAMBDA(A,B,IFS(AND(MOD(A,13)=1,B=1),INDEX(YU,ROUNDDOWN(A/13,0)+1),AND(MOD(A,13)=0,B=1),INDEX(YU,ROUNDDOWN(A/13,0))&" Total",AND(B=2,MOD(A,13)=1),1,AND(B=2,MOD(A,13)=4),2,AND(B=2,MOD(A,13)=7),3,AND(B=2,MOD(A,13)=10),4,AND(B=3,MOD(A,13)<>0),TEXT(MOD(A,13)*30,"MMM"),AND(B=4,MOD(A,13)<>0),SUM(FILTER(S,(M=MOD(A,13)*(Y=INDEX(YU,ROUNDDOWN(A/13,0)+1))))),AND(B=5,MOD(A,13)),SUM(FILTER(S,(M=MOD(A,13)*(Y=INDEX(YU,ROUNDDOWN(A/13,0)+1)))))/SUM(FILTER(S,Y=INDEX(YU,ROUNDDOWN(A/13,0)+1))),AND(B=4,MOD(A,13)=0),SUM(FILTER(S,Y=INDEX(YU,ROUNDDOWN(A/14,0)+1))),AND(B=5,MOD(A,13)=0),1,1,"")))))

Excel solution 4 for Pivot Table by Agent, proposed by Sunny Baggu:

=LET(
 _u, UNIQUE(YEAR(A2:A731)),
 REDUCE(
 {"Year", "Quarter", "Month", "Total Sale", "Sale %"},
 _u,
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _q, TOCOL(EXPAND(SEQUENCE(4), , 3, ""), , 0),
 _m, TEXT(EDATE(DATE(y, 1, 1), SEQUENCE(12) - 1), "mmm"),
 _ts, MAP(
 SEQUENCE(12),
 LAMBDA(a, SUM((YEAR(A2:A731) = y) * B2:B731 * (MONTH(A2:A731) = a)))
 ),
 _s, MAP(_ts, LAMBDA(b, b / SUM(_ts))) * 100,
 VSTACK(
 IFNA(HSTACK(y, _q, _m, _ts, _s), ""),
 HSTACK(y & " Total", "", "", SUM(_ts), 100)
 )
 )
 )
 )
 )
)

Excel solution 5 for Pivot Table by Agent, proposed by Md. Zohurul Islam:

=LET(dt,A2:A731,sls,B2:B731,
hdr,{"Year","Quarter","Month","Total Sale","Sale %"},
yr,YEAR(dt),m,MONTH(dt),
a,GROUPBY(HSTACK(yr,m,TEXT(dt,"mmm")),sls,SUM,0,0),
b,CHOOSECOLS(a,1,3,4),
unq,UNIQUE(TAKE(b,,1)),
d,REDUCE(hdr,unq,LAMBDA(j,k,LET(
 p,FILTER(b,TAKE(b,,1)=k),
 q,CEILING(MONTH(--(1&CHOOSECOLS(p,2)))/12*4,1),
 qtr,DROP(REDUCE("",UNIQUE(q),LAMBDA(x,y,VSTACK(x,TAKE(IFNA(HSTACK(y,FILTER(CHOOSECOLS(p,2),q=y)),""),,1)))),1),
 s,SUM(TAKE(p,,-1)),
 prc,MAP(TAKE(p,,-1),LAMBDA(x,x/s)),
 r,HSTACK(k&" Total","","",s,s/s),
 t,HSTACK(k,qtr,DROP(p,,1),prc),
 u,IFNA(VSTACK(t,r),""),
 v,VSTACK(j,u),v))),
d)

Excel solution 6 for Pivot Table by Agent, proposed by Miguel Angel Franco García:

=LET(añosunicos;UNICOS(AÑO(A2:A731));mesesunicos;APILARV(UNICOS(TEXTO(A2:A731;"mmm"));" ");mesesdobles;APILARV(mesesunicos;mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(A2:A731);AÑO(A2:A731);B2:B731;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);trimestres;SI(mesesdobles="ene";1;SI(mesesdobles="abr";2;SI(mesesdobles="jul";3;SI(mesesdobles="Oct";4;""))));resul;EXCLUIR(APILARH(modeloañostotal;trimestres;mesesdobles;APILARV(total1;total2));-1);resul)

Excel solution 7 for Pivot Table by Agent, proposed by Miguel Angel Franco García:

=LET(añosunicos;UNICOS(AÑO(A2:A731));mesesunicos;APILARV(UNICOS(TEXTO(A2:A731;"mmm"));" ");mesesdobles;APILARV(mesesunicos;" ";mesesunicos;" ");añosytotales;SI(mesesunicos="ene";ENFILA(añosunicos);SI(mesesunicos=" ";"Total "&ENFILA(añosunicos);""));modeloañostotal;ENCOL(TRANSPONER(añosytotales));pivota;PIVOTARPOR(MES(A2:A731);AÑO(A2:A731);B2:B731;APILARH(SUMA;PORCENTAJEDE));total1;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=TOMAR(añosunicos;1));2);total2;EXCLUIR(FILTRAR(pivota;TOMAR(pivota;1)=ELEGIRFILAS(añosunicos;2));2);trimestres;SI(mesesdobles="ene";1;SI(mesesdobles="abr";2;SI(mesesdobles="jul";3;SI(mesesdobles="Oct";4;""))));EXCLUIR(APILARH(modeloañostotal;trimestres;mesesdobles;APILARV(total1;total2));-2))

Solving the challenge of Pivot Table by Agent with Python

Python solution 1 for Pivot Table by Agent, proposed by Luan Rodrigues:

parte 1
import pandas as pd
import numpy as np
file_path = 'PYPQ_Challenge_173PQ_Challenge_173.xlsx'
df = pd.read_excel(file_path, usecols='A:B')
df['Year'] = df['Date'].dt.year
df['year'] = df['Date'].dt.year
df['Quarter'] = df['Date'].dt.quarter
df['Mn'] = df['Date'].dt.month
df['Month'] = df['Date'].dt.month_name().str.slice(stop=3)
df_gpr = df.groupby(['Year','year', 'Quarter', 'Month','Mn'])['Sale'].sum().reset_index(name='Total Sales')
df_gpr = df_gpr.sort_values(['Mn','Year'])
def calculate_sales(group):
 total_sales = group["Total Sales"].sum()
 group["Sales %"] = df_gpr["Total Sales"] / total_sales
 return group
df_result = df_gpr.groupby("Year").apply(calculate_sales).reset_index(drop=True)
df_year = df_result.groupby('Year').agg({'Total Sales': 'sum', 'Sales %': 'sum'}).reset_index()
df_year['Y'] = 'Total ' + df_year['Year'].astype('str')
df_year['year'] = df_year['Year']
df_year['Month'] = 'null'
df_year['Mn'] = 13 
df_year.drop(columns=['Year'], inplace=True)
df_year.columns = ['Total Sales', 'Sales %', 'Year','year', 'Month','Mn']

Python solution 2 for Pivot Table by Agent, proposed by Luan Rodrigues:

parte 2
df_total = pd.concat([df_result, df_year]).sort_values(['year','Mn']).reset_index(drop=True)
trimestre = ['Jan', 'Apr', 'Jul', 'Oct']
ano = ['Jan', 'null']
df_total['Y'] = np.where(df_total['Month'].isin(ano), df_total['Year'], 'null')
df_total['Q'] = np.where(df_total['Month'].isin(trimestre), df_total['Quarter'], 'null')
df_total.drop(columns=['Year', 'Quarter','year'], inplace=True)
df_total.columns = ['Month','Mn', 'Total Sales', 'Sales %', 'Year', 'Quarter']
new_order = ['Year', 'Quarter', 'Month', 'Total Sales', 'Sales %']
df_total = df_total[new_order]
print(df_total)

Solving the challenge of Pivot Table by Agent with R

R solution 1 for Pivot Table by Agent, proposed by Anil Kumar Goyal:

library(readxl)
library(janitor)
library(tidyverse)
df <- read_xlsx("PQ/PQ_Challenge_173.xlsx", range = cell_cols(LETTERS[1:2]))
df %>% 
 group_by(
 Year = year(Date),
 Quarter = as.factor(quarter(Date)),
 Month = month(Date, abbr = TRUE, label = TRUE)
 ) %>% 
 summarise(
 `Total Sale` = sum(Sale),
 .groups = 'drop'
 ) %>% 
 mutate(
 `Sale %` = scales::percent(`Total Sale`/sum(`Total Sale`),
 accuracy = 1),
 .by = Year
 ) %>% 
 group_split(Year) %>% 
 map_dfr(janitor::adorn_totals) %>% 
 mutate(`Sale %` = ifelse(`Sale %` == "-", "100%", `Sale %`))

Solving the challenge of Pivot Table by Agent with Power Query

Solving the challenge of Pivot Table by Agent with Excel

Solving the challenge of Pivot Table by Agent with Python

Solving the challenge of Pivot Table by Agent with R

Leave a Reply Cancel reply