Home » Add Hierarchical Location Indexes

Add Hierarchical Location Indexes

Add the indexes for Country, State and City

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

Solving the challenge of Add Hierarchical Location Indexes with Power Query

Power Query solution 1 for Add Hierarchical Location Indexes, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ls = Table.ToColumns(Source), 
  Fn = (c) =>
    List.Accumulate(
      {0 .. Table.RowCount(Source) - 1}, 
      {}, 
      (a, n) =>
        a
          & {
            (
              if c = 0 then
                List.Last(a, 0)
              else
                (if Ls{c - 1}{n} <> null then 0 else List.Last(a, 0))
            )
              + Number.From(Ls{c}{n} <> null)
          }
    ), 
  Cn = Table.ColumnNames(Source), 
  Res = Table.FromColumns(
    Ls & List.Transform({0 .. List.Count(Ls) - 1}, each Fn(_)), 
    Cn & List.Transform(Cn, each "index " & _)
  )
in
  Res
Power Query solution 2 for Add Hierarchical Location Indexes, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  FilledDown = Table.FillDown(Source, {"Country", "State"}), 
  AddedIndexes = Table.ReorderColumns(
    Table.ExpandTableColumn(
      Table.AddIndexColumn(
        Table.Group(
          FilledDown, 
          {"Country"}, 
          {
            {
              "CG", 
              each Table.ExpandTableColumn(
                Table.AddIndexColumn(
                  Table.Group(_, {"State"}, {{"SG", each Table.AddIndexColumn(_, "Index City", 1)}}), 
                  "Index State", 
                  1
                ), 
                "SG", 
                {"City", "Index City"}
              )
            }
          }
        ), 
        "Index Country", 
        1
      ), 
      "CG", 
      {"State", "City", "Index State", "Index City"}
    ), 
    {"Country", "State", "City", "Index Country", "Index State", "Index City"}
  ), 
  Solution = Table.FromColumns(
    Table.ToColumns(Source) & List.Skip(Table.ToColumns(AddedIndexes), 3), 
    Table.ColumnNames(AddedIndexes)
  )
in
  Solution
Power Query solution 3 for Add Hierarchical Location Indexes, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fill = Table.FillDown(Source, {"Country", "State"}), 
  Group = Table.AddIndexColumn(
    Table.Group(
      Fill, 
      {"Country"}, 
      {
        {
          "All", 
          each 
            let
              a = _[[State], [City]], 
              b = Table.AddIndexColumn(
                Table.Group(
                  a, 
                  {"State"}, 
                  {
                    {
                      "All", 
                      each 
                        let
                          a = [[City]], 
                          b = Table.AddIndexColumn(a, "Index City", 1)
                        in
                          b
                    }
                  }
                ), 
                "Index State", 
                1
              )
            in
              b
        }
      }
    ), 
    "Index Country", 
    1
  ), 
  Expand1 = Table.ExpandTableColumn(Group, "All", {"State", "All", "Index State"}), 
  Expand2 = Table.ExpandTableColumn(Expand1, "All", {"City", "Index City"}), 
  Merge = Table.NestedJoin(Source, {"City"}, Expand2, {"City"}, "Expand2l", JoinKind.LeftOuter), 
  Sol = Table.ExpandTableColumn(Merge, "Expand2l", {"Index Country", "Index State", "Index City"})
in
  Sol
Power Query solution 4 for Add Hierarchical Location Indexes, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  du = Table.DuplicateColumn(Fonte, "Country", "Country "), 
  dp = Table.DuplicateColumn(du, "State", "State "), 
  pa = Table.FillDown(dp, {"Country", "State"}), 
  gp = Table.AddIndexColumn(
    Table.Group(
      pa, 
      {"Country"}, 
      {
        {
          "Contagem", 
          each Table.AddIndexColumn(
            Table.Group(_, {"State"}, {{"Count", each Table.AddIndexColumn(_, "Index City", 1, 1)}}), 
            "Index State", 
            1, 
            1
          )
        }
      }
    ), 
    "Index Country", 
    1, 
    1
  ), 
  exp = Table.ExpandTableColumn(gp, "Contagem", Table.ColumnNames(gp[Contagem]{0})), 
  res = Table.ExpandTableColumn(
    exp, 
    "Count", 
    List.RemoveFirstN(Table.ColumnNames(exp[Count]{0}), 2)
  )[[#"Country "], [#"State "], [City], [Index Country], [Index State], [Index City]]
in
  res
Power Query solution 5 for Add Hierarchical Location Indexes, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData88"]}[Content], 
  Transform = List.Accumulate(
    Table.ToRecords(Source), 
    [R = {}, Idx = {0, 0, 0}], 
    (s, c) =>
      let
        _NewIdx = 
          if (c[Country] <> null) then
            {s[Idx]{0} + 1, 1, 1}
          else if (c[State] <> null) then
            {s[Idx]{0}, s[Idx]{1} + 1, 1}
          else
            {s[Idx]{0}, s[Idx]{1}, s[Idx]{2} + 1}, 
        _NewR = c
          & [#"Index Country" = _NewIdx{0}, #"Index State" = _NewIdx{1}, #"Index City" = _NewIdx{2}]
      in
        [R = s[R] & {_NewR}, Idx = _NewIdx]
  )[R], 
  ToTable = Table.FromRecords(Transform)
in
  ToTable
Power Query solution 6 for Add Hierarchical Location Indexes, proposed by Roy Wilson: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ChgTyp = Table.TransformColumnTypes(
    Source, 
    {{"Country", type text}, {"State", type text}, {"City", type text}}
  ), 
  DupCntry = Table.DuplicateColumn(ChgTyp, "Country", "Cntry"), 
  DupSt = Table.DuplicateColumn(DupCntry, "State", "St"), 
  FlDwn = Table.FillDown(DupSt, {"Cntry", "St"}), 
  GpCntry = Table.Group(
    FlDwn, 
    {"Cntry"}, 
    {
      {
        "a", 
        each _, 
        type table [
          Country = nullable text, 
          State = nullable text, 
          City = nullable text, 
          Cntry = nullable text, 
          St = nullable text
        ]
      }
    }
  ), 
  CtIndex = Table.AddIndexColumn(GpCntry, "Index Country", 1), 
  Expnd1 = Table.ExpandTableColumn(CtIndex, "a", {"Country", "State", "City", "St"}), 
  GpSt = Table.Group(
    Expnd1, 
    {"St"}, 
    {
      {
        "b", 
        each _, 
        type table [
          Cntry = nullable text, 
          Country = nullable text, 
          State = nullable text, 
          City = nullable text, 
          St = nullable text, 
          Index Country = number
        ]
      }
    }
  ), 
  StIndex = Table.AddIndexColumn(GpSt, "Index State", 1), 
  Expnd2 = Table.ExpandTableColumn(StIndex, "b", {"Country", "State", "City", "Index Country"}), 
  DropCol = Table.RemoveColumns(Expnd2, "St"), 
  CtInd = Table.AddIndexColumn(DropCol, "Index City", 1)
in
  CtInd

Solving the challenge of Add Hierarchical Location Indexes with Excel

Excel solution 1 for Add Hierarchical Location Indexes, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:C12,h,A1:C1,VSTACK(HSTACK(h,"index "&h),REDUCE(z&"",
SEQUENCE(COLUMNS(z)),LAMBDA(a,i,HSTACK(a,SCAN(,SEQUENCE(ROWS(z)),LAMBDA(x,n,IF(i-1,(INDEX(z,n,i-1)=0)*x,x)+(INDEX(z,n,i)>0))))))))
Excel solution 2 for Add Hierarchical Location Indexes, proposed by Rick Rothstein: 
=VSTACK("Index "&A1:C1,HSTACK(SCAN(0,A2:A12,LAMBDA(a,x,a+(x<>""))),SCAN(0,B2:B12,LAMBDA(a,x,IF(OFFSET(x,,-1)="",a+(x<>""),1))),SCAN(0,C2:C12,LAMBDA(a,x,IF(OFFSET(x,,-1)="",a+1,1)))))
Excel solution 3 for Add Hierarchical Location Indexes, proposed by John V.: 
=LET(c,A2:A12,s,B2:B12,h,A1:C1,f,LAMBDA(x,y,SCAN(,SEQUENCE(ROWS(x)),LAMBDA(a,v,IF(INDEX(x,v)=0,a+(INDEX(y,v)>0),1)))),VSTACK(HSTACK(h,"Index "&h),HSTACK(A2:C12&"",f(ROW(c)^0-1,c),f(c,s),f(s,C2:C12))))

✅=LET(d,A2:C12,h,A1:C1,VSTACK(HSTACK(h,"Index "&h),REDUCE(d&"",SEQUENCE(COUNTA(h)),LAMBDA(b,w,HSTACK(b,SCAN(,SEQUENCE(ROWS(d)),LAMBDA(a,v,IF(IF(w>1,INDEX(d,v,w-1),)=0,a+(INDEX(d,v,w)>0),1))))))))
Excel solution 4 for Add Hierarchical Location Indexes, proposed by محمد حلمي: 
=VSTACK(HSTACK(A1:C1,"Index "&A1:C1),
HSTACK(A2:B12&"",REDUCE(C2:C12,A2:C2,LAMBDA(a,v,
HSTACK(a,SCAN(0,OFFSET(v,,,11),LAMBDA(i,j,
IFERROR(IF(OFFSET(j,,-1)>0,1,i+(j>0)),i+(j>0)))))))))
Excel solution 5 for Add Hierarchical Location Indexes, proposed by محمد حلمي: 
=LET(i,A1:C1,b,B2:B12,r,LAMBDA(x,r,SCAN(0,x,LAMBDA(a,d,
IF(d>"",IF(r,a+1,1),IF(r,a,a+1))))),s,r(A2:A12,1),VSTACK(
HSTACK(i,"Index "&i),HSTACK(A2:C12&"",s,DROP(REDUCE(
0,UNIQUE(s),LAMBDA(a,d,VSTACK(a,r(FILTER(b,d=s),1)))),1),
r(b,0))))
Excel solution 6 for Add Hierarchical Location Indexes, proposed by محمد حلمي: 
=MAP(A2:C12,LAMBDA(a,LOOKUP("z",TAKE(a:A2,,-1))))

Bo Rydobon

=TRANSPOSE(SCAN("",TRANSPOSE(A2:C12),LAMBDA(a,d,IF(d>0,d,a))))
Excel solution 7 for Add Hierarchical Location Indexes, proposed by محمد حلمي: 
=VSTACK(HSTACK(A1:C1,"Index "&A1:C1),
HSTACK(A2:B12&"",REDUCE(C2:C12,A2:C2,LAMBDA(a,v,
HSTACK(a,SCAN(0,OFFSET(v,,,11),LAMBDA(a,v,
IFERROR(IF(OFFSET(v,,-1)>0,1,a+(v>0)),a+(v>0)))))))))
Excel solution 8 for Add Hierarchical Location Indexes, proposed by Oscar Mendez Roca Farell: 
=LET(_d, A2:C12, _f, LAMBDA(t, MAP(t, LAMBDA(b, LOOKUP(2,1/(INDEX(t,1):b>""""), INDEX(t, 1):b)))),_c, LAMBDA(z, LET(_u, UNIQUE(z), MMULT(--(z=TOROW(_u)), SEQUENCE(COUNTA(_u))))),  REDUCE(_d, SEQUENCE(COLUMNS(_d)), LAMBDA(i, x, HSTACK(i, IF(x-1, DROP(REDUCE("", UNIQUE(_f(INDEX(_d, ,x-1))), LAMBDA(j, y, VSTACK( j,_c(FILTER(_f(INDEX(_d, ,x)),_f(INDEX(_d, ,x-1))=y))))), 1),_c(_f(INDEX(_d, ,1))))))))
Excel solution 9 for Add Hierarchical Location Indexes, proposed by Sunny Baggu: 
=LET(_ftbl,TRANSPOSE(SCAN("",TRANSPOSE(A2:C12),LAMBDA(a,v,IF(LEN(v)>1,v,a)))),_ic,SCAN(0,A2:A12,LAMBDA(a,v,IF(LEN(v)>1,a+1,a))),
_e2,LAMBDA(k,CHOOSECOLS(_ftbl,k)),
_e1,LAMBDA(_col1,_col2,DROP(REDUCE("",UNIQUE(_col1),LAMBDA(x,y,VSTACK(x,LET(_arr,FILTER(_col2,_col1=y),SCAN(1,SEQUENCE(ROWS(_arr)),LAMBDA(a,v,IFERROR(IF(CHOOSEROWS(_arr,v)<>CHOOSEROWS(_arr,v-1),a+1,a),1))))))),1)),HSTACK(IF(A2:C12="","",A2:C12),_ic,_e1(_e2(1),_e2(2)),_e1(_e2(2),_e2(3))))

Solving the challenge of Add Hierarchical Location Indexes with Python in Excel

Python in Excel solution 1 for Add Hierarchical Location Indexes, proposed by Alejandro Campos: 
df = xl("A1:C12", headers=True)
df['Country'] = df['Country'].ffill()
df['State'] = df['State'].ffill()
df['Index Country'] = df['Country'].ne(df['Country'].shift()).cumsum()
df['Index State'] = df.groupby('Country')['State'].transform(lambda x: x.ne(x.shift()).cumsum())
df['Index City'] = df.groupby(['Country', 'State']).cumcount() + 1
df['State'] = xl("B1:B12", headers=True).fillna(' ')
df['Country'] = xl("A1:A12", headers=True).fillna(' ')
df.reset_index(drop=True, inplace=True)
df

&&&

Leave a Reply