Hour Bucket in Excel Power Query

S

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Hope you are doing well !!!

I need a small help in Power Query.

I want to make hourly TAT bucket based on column A*, currently, I am using lookup function in excel.


Could you please help me apply a similar formula/function in Power Query?

My Office Version - Office 365Pro.

The current formula I am using in excel:-

=LOOKUP(A2,{0,1,2,3,5,7,15},{"1. <=24","2. >24hrs <=48hrs ","3. > 48hrs <= 72hrs ","4. > 72hrs <= 120hrs","5.>120hrs<= 168hrs","6. > 7 days <=15 days ","7. >15 days"})

The required result using Power Query is as follows

Book2
AB
1TAT in HrsHrs Bucket
226:522. >24hrs <=48hrs
344:202. >24hrs <=48hrs
47:011. <=24
5332:536. > 7 days <=15 days
625:542. >24hrs <=48hrs
753:263. > 48hrs <= 72hrs
81:071. <=24
93:181. <=24
1074:044. > 72hrs <= 120hrs
11238:566. > 7 days <=15 days
1228:102. >24hrs <=48hrs
1322:521. <=24
1451:523. > 48hrs <= 72hrs
1525:012. >24hrs <=48hrs
1621:251. <=24
1725:112. >24hrs <=48hrs
188:361. <=24
1920:091. <=24
20453:457. >15 days
21102:474. > 72hrs <= 120hrs
227:281. <=24
2368:003. > 48hrs <= 72hrs
24171:206. > 7 days <=15 days
25500:537. >15 days
266:071. <=24
2720:141. <=24
2823:191. <=24
2943:512. >24hrs <=48hrs
30100:384. > 72hrs <= 120hrs
3122:511. <=24
Sheet1
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Zeszyt1
BCDEFGH
1Source (Tabela1)PQ Result - FirstPQ Result - Second
2TAT in HrsTAT in HrsHrs Bucket PQTAT in HrsHrs Bucket PQ
326:52:1826:52:182. >24hrs <=48hrs26:52:182. >24hrs <=48hrs
444:20:3444:20:342. >24hrs <=48hrs44:20:342. >24hrs <=48hrs
57:01:307:01:301. <=247:01:301. <=24
6332:53:11332:53:116. > 7 days <=15 days332:53:116. > 7 days <=15 days
725:54:0225:54:022. >24hrs <=48hrs25:54:022. >24hrs <=48hrs
853:26:2553:26:253. > 48hrs <= 72hrs53:26:253. > 48hrs <= 72hrs
91:07:581:07:581. <=241:07:581. <=24
103:18:563:18:561. <=243:18:561. <=24
1174:04:4074:04:404. > 72hrs <= 120hrs74:04:404. > 72hrs <= 120hrs
12238:56:59238:56:596. > 7 days <=15 days238:56:596. > 7 days <=15 days
1328:10:3728:10:372. >24hrs <=48hrs28:10:372. >24hrs <=48hrs
1422:52:1922:52:191. <=2422:52:191. <=24
1551:52:4051:52:403. > 48hrs <= 72hrs51:52:403. > 48hrs <= 72hrs
1625:01:1425:01:142. >24hrs <=48hrs25:01:142. >24hrs <=48hrs
1721:25:2921:25:291. <=2421:25:291. <=24
1825:11:4525:11:452. >24hrs <=48hrs25:11:452. >24hrs <=48hrs
198:36:218:36:211. <=248:36:211. <=24
2020:09:0120:09:011. <=2420:09:011. <=24
21453:45:26453:45:267. >15 days453:45:267. >15 days
22102:47:17102:47:174. > 72hrs <= 120hrs102:47:174. > 72hrs <= 120hrs
237:28:077:28:071. <=247:28:071. <=24
2468:00:3268:00:323. > 48hrs <= 72hrs68:00:323. > 48hrs <= 72hrs
25171:20:58171:20:586. > 7 days <=15 days171:20:586. > 7 days <=15 days
26500:53:44500:53:447. >15 days500:53:447. >15 days
276:07:436:07:431. <=246:07:431. <=24
2820:14:5620:14:561. <=2420:14:561. <=24
2923:19:0923:19:091. <=2423:19:091. <=24
3043:51:0943:51:092. >24hrs <=48hrs43:51:092. >24hrs <=48hrs
31100:38:30100:38:304. > 72hrs <= 120hrs100:38:304. > 72hrs <= 120hrs
3222:51:2422:51:241. <=2422:51:241. <=24
Sheet1


M-code (First)
Power Query: 
let
    TFC = Table.FromColumns({{0,1,2,3,5,7,15},{"1. <=24","2. >24hrs <=48hrs","3. > 48hrs <= 72hrs","4. > 72hrs <= 120hrs","5.>120hrs<= 168hrs","6. > 7 days <=15 days","7. >15 days"}}, {"Days", "Hrs Bucket PQ"}),
    AllDays = Table.FromColumns({{0..15}}, {"Days"}),
    #"Merged Queries" = Table.NestedJoin(AllDays, {"Days"}, TFC, {"Days"}, "TFC", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "TFC", {"Hrs Bucket PQ"}, {"Hrs Bucket PQ"}),
    #"Filled Down" = Table.Buffer(Table.FillDown(#"Expanded {0}",{"Hrs Bucket PQ"})),
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAT in Hrs", type number}}),
    DaysOnly = Table.AddColumn(#"Changed Type", "Days", each Number.IntegerDivide([TAT in Hrs], 1), Int64.Type),
    #"Added Index" = Table.AddIndexColumn(DaysOnly, "Indeks", 0, 1, Int64.Type),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index", {"Days"}, #"Filled Down", {"Days"}, "DaysOnly", JoinKind.LeftOuter),
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries1", "DaysOnly", {"Hrs Bucket PQ"}, {"Hrs Bucket PQ"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded {0}1",null,"7. >15 days",Replacer.ReplaceValue,{"Hrs Bucket PQ"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Indeks", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"TAT in Hrs", "Hrs Bucket PQ"})
in
    #"Removed Other Columns"

M-Code (second)
[CODE=pq]
let
    TFC = Table.FromColumns({{0,1,2,3,5,7,15},{"1. <=24","2. >24hrs <=48hrs","3. > 48hrs <= 72hrs","4. > 72hrs <= 120hrs","5.>120hrs<= 168hrs","6. > 7 days <=15 days","7. >15 days"}}, {"Days", "Hrs Bucket PQ"}),
    AllDays = Table.FromColumns({{0..15}}, {"Days"}),
    #"Merged Queries" = Table.NestedJoin(AllDays, {"Days"}, TFC, {"Days"}, "TFC", JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "TFC", {"Hrs Bucket PQ"}, {"Hrs Bucket PQ"}),
    #"Filled Down" = Table.FillDown(#"Expanded {0}",{"Hrs Bucket PQ"}),
    BucketList = List.Buffer(#"Filled Down"[Hrs Bucket PQ]),
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAT in Hrs", type number}}),
    HrsBucked = Table.AddColumn(#"Changed Type", "Hrs Bucket PQ", each BucketList{Number.IntegerDivide([TAT in Hrs], 1)}, type text),
    #"Replaced Errors" = Table.ReplaceErrorValues(HrsBucked, {{"Hrs Bucket PQ", "7. >15 days"}})
in
    #"Replaced Errors"
[/CODE]
 
Upvote 0
Below code equals to your LOOKUP formula:

Power Query: 
= Table.AddColumn(Source,"Hrs Bucket",each {"1. <=24","2. >24hrs <=48hrs","3. > 48hrs <= 72hrs","4. > 72hrs <= 120hrs","5.>120hrs<= 168hrs","6. > 7 days <=15 days","7. >15 days"}{List.Count(List.Select({1,2,3,5,7,15},(x)=>x<=[#"TAT in Hrs"]))})

However, there is a potential bug for your LOOKUP formula and above code, if A2 equals to 1, your formula will return "2. >24hrs <=48hrs ".

Below code may fix the problem:
Power Query: 
= Table.AddColumn(Source,"Hrs Bucket",each {"1. <=24","2. >24hrs <=48hrs","3. > 48hrs <= 72hrs","4. > 72hrs <= 120hrs","5.>120hrs<= 168hrs","6. > 7 days <=15 days","7. >15 days"}{List.Count(List.Select(List.Transform({1,2,3,5,7,15},(y)=>y+1/Number.Power(10,9)),(x)=>x<=[#"TAT in Hrs"]))})

Book1.xlsx
ABCD
1TAT in HrsTAT in HrsHrs Bucket
224:00:0011. <=24
344:20:341.8476157412. >24hrs <=48hrs
407:01:300.2927083331. <=24
5332:53:1113.87026626. > 7 days <=15 days
625:54:021.0791898152. >24hrs <=48hrs
753:26:252.2266782413. > 48hrs <= 72hrs
801:07:580.0471990741. <=24
903:18:560.1381481481. <=24
1074:04:403.0865740744. > 72hrs <= 120hrs
11238:56:599.9562384266. > 7 days <=15 days
1228:10:371.1740393522. >24hrs <=48hrs
1322:52:190.9529976851. <=24
1451:52:402.1615740743. > 48hrs <= 72hrs
1525:01:141.0425231482. >24hrs <=48hrs
1621:25:290.8926967591. <=24
1725:11:451.0498263892. >24hrs <=48hrs
1808:36:210.3585763891. <=24
1920:09:010.8395949071. <=24
20453:45:2618.906550937. >15 days
21102:47:174.2828356484. > 72hrs <= 120hrs
2207:28:070.311192131. <=24
2368:00:322.8337037043. > 48hrs <= 72hrs
24171:20:587.1395601856. > 7 days <=15 days
25500:53:4420.870648157. >15 days
2606:07:430.2553587961. <=24
2720:14:560.8437037041. <=24
2823:19:090.9716319441. <=24
2943:51:091.82718752. >24hrs <=48hrs
30100:38:304.1934027784. > 72hrs <= 120hrs
3122:51:240.9523611111. <=24
Sheet16
 
Upvote 0
In post #4, i forgot to set cell format for column C:C, use format painter or set cell format to [hh]:mm:ss.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Hourly TAT Age Bucket
Replies
3
Views
807
FormR
FormR
L
  • Question
I would like to see the distribution by hour and day of these entries but don't have a clue where to start...
Replies
6
Views
986
leanheart
L
W
  • Question
Sort data with calculated fields gives unexpected results
Replies
4
Views
360
wpryan
W
S
  • Question
Filter the values.
Replies
6
Views
615
sekar_r24
S
davedec
  • Question
Forecast.ETS
Replies
2
Views
349
davedec
davedec

Forum statistics

Threads
1,214,965
Messages
6,122,499
Members
449,089
Latest member
Raviguru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top