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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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

A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
128
Fluff
Fluff
S
  • Question Question
Hourly TAT Age Bucket
Replies
3
Views
958
FormR
FormR

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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