Home » Populate Headers from Lookup Table

Populate Headers from Lookup Table

Generate Result table on the basis of problem tables. You need to populate Start headers from second table into first table.

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

Solving the challenge of Populate Headers from Lookup Table with Power Query

Power Query solution 1 for Populate Headers from Lookup Table, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Unpivot = 
    let
      l = Table.ToColumns(Table.RemoveLastN(Table.UnpivotOtherColumns(Source, {}, "S", "T")))
    in
      Table.Buffer(
        Table.FromColumns(l & {List.Skip(l{1}) & {1}}, {"Start", "Start Time", "End Time"})
      ), 
  T1 = Table.RenameColumns(
    Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
    {{"Start Time", "S"}, {"End Time", "E"}}
  ), 
  Sel = Table.AddColumn(
    T1, 
    "T", 
    each Table.TransformColumns(
      Table.SelectRows(
        Unpivot, 
        (u) =>
          u[Start Time]
            < Number.Mod(Number.From([E]), 1) and u[End Time]
            > Number.Mod(Number.From([S]), 1)
      ), 
      {
        {
          "Start Time", 
          (t) =>
            let
              a = Number.From([S]), 
              b = t + Number.IntegerDivide(a, 1)
            in
              if a > b then a else b
        }, 
        {
          "End Time", 
          (t) =>
            let
              a = Number.From([E]), 
              b = t + Number.IntegerDivide(a, 1)
            in
              if a > b then b else a
        }
      }
    )
  ), 
  Expand = Table.SelectColumns(
    Table.ExpandTableColumn(Sel, "T", {"Start", "Start Time", "End Time"}), 
    {"Fleet", "Start Time", "End Time", "Group", "Start"}
  )
in
  Expand
Power Query solution 2 for Populate Headers from Lookup Table, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  UnPiv = Table.UnpivotOtherColumns(
    Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
    {}, 
    "Start", 
    "From"
  ), 
  Int = Table.RemoveLastN(
    Table.AddColumn(
      UnPiv, 
      "To", 
      each 
        let
          l = List.RemoveLastN(UnPiv[From]) & {1}
        in
          l{List.PositionOf(l, [From]) + 1}
    )
  ), 
  AddInt = Table.ExpandTableColumn(
    Table.AddColumn(
      Source, 
      "T2", 
      each 
        let
          st = Number.From(DateTime.Time([Start Time])), 
          et = Number.From(DateTime.Time([End Time])), 
          s  = 1 / (24 * 60)
        in
          Table.SelectRows(
            Int, 
            each (st >= [From] and st <= [To] - s)
              or (et - s >= [From] and et - s <= [To])
              or ([From] >= st and [To] <= et)
          )
    ), 
    "T2", 
    {"Start", "From", "To"}
  ), 
  S = Table.RemoveColumns(
    Table.ReplaceValue(
      Table.ReplaceValue(
        AddInt, 
        each [Start Time], 
        each 
          let
            dt = Number.From([Start Time]), 
            d  = Number.RoundDown(dt), 
            t  = Number.Mod(dt, 1)
          in
            DateTime.From(d + (if t > [From] then t else [From])), 
        Replacer.ReplaceValue, 
        {"Start Time"}
      ), 
      each [End Time], 
      each 
        let
          dt = Number.From([End Time]), 
          d  = Number.RoundDown(dt), 
          t  = Number.Mod(dt, 1)
        in
          DateTime.From(d + (if t > [To] then [To] else t)), 
      Replacer.ReplaceValue, 
      {"End Time"}
    ), 
    {"From", "To"}
  )
in
  S
Power Query solution 3 for Populate Headers from Lookup Table, proposed by Eric Laforce: 
let
 STimes = let 
 _T = Excel.CurrentWorkbook(){[Name="tData89_Start"]}[Content],
 _Transform = Table.RemoveLastN(Table.UnpivotOtherColumns(_T, {}, "Start", "Hour"))
 in Table.Buffer(_Transform),
 Source = Excel.CurrentWorkbook(){[Name="tData89"]}[Content],
 MergeP = Table.CombineColumnsToRecord(Source, "Period", {"Start Time", "End Time"}),
 Tranf_P = Table.TransformColumns(MergeP, {"Period", (r)=> let
 _TS = r[Start Time], _TE = r[End Time],
 _Hours = let
 _L1 = List.Generate(()=>_TS, each _<_TE, each _+hashtag#duration(0,0,60-Time.Minute(_),0) )
 in List.Skip(_L1) & {_TE},
 _Periods = List.Accumulate(_Hours, [ts=_TS, i=-1, r={}], (s,c)=>let
 _i = List.Count(List.Select(STimes[Hour], each Time.From(_)

Solving the challenge of Populate Headers from Lookup Table with Excel

Excel solution 1 for Populate Headers from Lookup Table, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:D8,w,A12:D14,y,DROP(TOCOL(w,3),-1),
REDUCE(HSTACK(A1:D1,"Start"),SEQUENCE(ROWS(z)),LAMBDA(a,n,LET(
b,INDEX(z,n,2),c,MOD(b,1),d,MOD(INDEX(z,n,3),1),i,INT(b),s,MATCH(c,y),t,SEQUENCE(MATCH(d,y)-s+1,,s),
e,INDEX(y,t),f,IFERROR(INDEX(y,t+1),1),VSTACK(a,HSTACK(IF(t,INDEX(z,n,1)),i+IF(c>e,c,e),i+IF(f>d,d,f),IF(t,INDEX(z,n,4)),INDEX(TOCOL(IFNA(A11:D11,w)),t)))))))
Excel solution 2 for Populate Headers from Lookup Table, proposed by محمد حلمي: 
=REDUCE(HSTACK(A1:D1,"Start"),B2:B8,LAMBDA(a,d,LET(
h,OFFSET(d,,1),r,TOCOL(A12:D14),c,MOD(d,1),u,TAKE(d-c,)+FILTER(r,(r>c)*(MOD(OFFSET(d,,1),1)>r)),e,TOCOL(
VSTACK(1,u),2),y,TOCOL(VSTACK(d,u),2),VSTACK(a,HSTACK(IF(e,OFFSET(d,,-1)),y,TOCOL(VSTACK(u,h),2),IF(e,OFFSET(d,,2)),XLOOKUP(ROUND(MOD(y,1),9),ROUND(r,9),TAKE(TOCOL(IFNA(A11:D11,r)),ROWS(r)),,-1))))))
Excel solution 3 for Populate Headers from Lookup Table, proposed by محمد حلمي: 
=REDUCE(HSTACK(A1:D1,"Start"),SEQUENCE(ROWS(A2:D8)),LAMBDA(a,d,LET(x,A2:D8,v,INDEX(x,d,2),h,INDEX(x,
d,3),r,TOCOL(A12:D14),c,MOD(v,1),u,TAKE(v-c,)+FILTER(
r,(r>c)*(MOD(h,1)>r)),e,TOCOL(VSTACK(1,u),2),y,
TOCOL(VSTACK(v,u),2),VSTACK(a,HSTACK(IF(e,INDEX(x,
d,1)),y,TOCOL(VSTACK(u,h),2),IF(e,INDEX(x,d,4)),
XLOOKUP(ROUND(MOD(y,1),9),ROUND(r,9),TAKE(
TOCOL(IFNA(A11:D11,r)),ROWS(r)),,-1))))))
Excel solution 4 for Populate Headers from Lookup Table, proposed by Oscar Mendez Roca Farell: 
=LET(_m, A2:D8, REDUCE(HSTACK(A1:D1, "Star"), ROW(_m)-1, LAMBDA(i, x, LET(_t,A12:D14,_b,A11:D11,st, MOD(INDEX(_m, x, 2), 1), et, MOD(INDEX(_m, x, 3), 1),_e, REPT(_t, 1/(st<_t)/(et>_t)/(_t<>"")),_i, VSTACK(INDEX(_m, x, 2), INT(INDEX(_m, x, 2)/1)+TOCOL(_e, 3)),_f, VSTACK(DROP(_i, 1), INDEX(_m, x, 3)),_c, TAKE(TOCOL(REPT(_b, 1/(ABS(_t-st)=MIN(ABS(_t-st)))), 3), 1),_r, VSTACK(_c, TOCOL(REPT(_b, _e>0), 3)), VSTACK(i, IFERROR(HSTACK(IF(_i, INDEX(_m, x, 1)),_i,_f,IF(_i, INDEX(_m, x, 4)),_r), HSTACK(INDEX(_m, x, ),_c)))))))

&&&

Leave a Reply