Calculated field filtered tables

C

comonand

New Member
Joined
Feb 7, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Have a spreadsheet where im trying to calculate costs for times of day, that also change at the weekend. (I can do it with vlookups but there will be more criteria like package type)


PQExample.xlsx
ABCDEFGHIJKLMN
1DateTime Of DayHours UsedDayWeekDayWeekdayTimeTariffTypeTariffTypeCost
201/12/202200:00151500:00MediumVery Low25
301/12/202201:00352501:00MediumLow50
401/12/202202:00453502:00MediumMedium75
501/12/202203:00254503:00MediumHigh150
601/12/202204:00155504:00MediumVery High200
701/12/202205:00556505:00Medium
801/12/202206:00257506:00Medium
901/12/202207:0085507:00Medium
1001/12/202208:0095508:00Medium
1101/12/202209:0085509:00Very Low
1201/12/202210:0075510:00Very Low
1301/12/202211:0075511:00Very Low
1401/12/202212:0085512:00Very Low
1501/12/202213:0095513:00Very Low
1601/12/202214:0065514:00Very Low
1701/12/202215:0055515:00Very Low
1801/12/202216:0095516:00Very Low
1901/12/202217:0095517:00Very Low
2001/12/202218:0055518:00Low
2101/12/202219:0045519:00Low
2201/12/202220:0035520:00Low
2301/12/202221:0015521:00Low
2401/12/202222:0025522:00Medium
2501/12/202223:0025523:00Medium
2602/12/202200:0026600:00Medium
2702/12/202201:0026601:00Medium
2802/12/202202:0036602:00Medium
2902/12/202203:0046603:00Medium
3002/12/202204:0026604:00Medium
3102/12/202205:0046605:00Medium
3202/12/202206:0016606:00Medium
3302/12/202207:0076607:00Medium
3402/12/202208:00106608:00Medium
3502/12/202209:0086609:00Very Low
3602/12/202210:0076610:00Very Low
3702/12/202211:0066611:00Very Low
3802/12/202212:0076612:00Very Low
3902/12/202213:0076613:00Very Low
4002/12/202214:0056614:00Very Low
4102/12/202215:0086615:00Very Low
4202/12/202216:0096616:00Very Low
4302/12/202217:0096617:00Very Low
4402/12/202218:0066618:00Low
4502/12/202219:0026619:00Low
4602/12/202220:0016620:00Low
4702/12/202221:0016621:00Low
4802/12/202222:0026622:00High
4902/12/202223:0016623:00High
5003/12/202200:0027700:00High
5103/12/202201:0067701:00High
5203/12/202202:0027702:00High
5303/12/202203:0017703:00High
5403/12/202204:0017704:00High
5503/12/202205:0047705:00High
5603/12/202206:0047706:00High
5703/12/202207:0047707:00High
5803/12/202208:0077708:00High
5903/12/202209:0027709:00Medium
6003/12/202210:0027710:00Medium
6103/12/202211:0027711:00Medium
6203/12/202212:0047712:00Medium
6303/12/202213:0017713:00Medium
6403/12/202214:0057714:00Medium
6503/12/202215:0057715:00Medium
6603/12/202216:0097716:00Medium
6703/12/202217:0097717:00High
6803/12/202218:0057718:00High
6903/12/202219:0047719:00High
7003/12/202220:0037720:00High
7103/12/202221:0017721:00High
7203/12/202222:0027722:00Very High
7303/12/202223:0027723:00Very High
Sheet1
Cell Formulas
RangeFormula
E2:E73E2=WEEKDAY([@Date])


PQExample.png

(Day linked to WeekDay to Weekday to TariffType)

Im trying to create a calculated field in the HoursUsed table that multiplies Hours Used Column by Cost Column (based on day of week and time of day)

If i do it with just time and not the day of week its

=TariffCost[Cost]*HoursUsed[Hours Used]

I think i need to use the filter function to filter the TariffType table or possibly the DayOfWeek table but am unsure how to use it in power query

Any suggestions/further reading, new to power query and everything im googling is not helping

Thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
I'm a BIG proponent of Power Query and the Data Model, but this is one place where a simple XLOOKUP would do fine.
Book1
BCDEFGHIJ
1DateTime Of DayHours UsedWeekdayTarrifTypeWeekdayTimeTariffType
212/01/202212:00:00 AM15Medium512:00:00 AMMedium
312/01/202201:00:00 AM35Medium501:00:00 AMMedium
412/01/202202:00:00 AM45Medium502:00:00 AMMedium
512/01/202203:00:00 AM25Medium503:00:00 AMMedium
612/01/202204:00:00 AM15Medium504:00:00 AMMedium
712/01/202205:00:00 AM55Medium505:00:00 AMMedium
812/01/202206:00:00 AM25Medium506:00:00 AMMedium
912/01/202207:00:00 AM85Medium507:00:00 AMMedium
1012/01/202208:00:00 AM95Medium508:00:00 AMMedium
1112/01/202209:00:00 AM85Very Low509:00:00 AMVery Low
1212/01/202210:00:00 AM75Very Low510:00:00 AMVery Low
1312/01/202211:00:00 AM75Very Low511:00:00 AMVery Low
1412/01/202212:00:00 PM85Very Low512:00:00 PMVery Low
1512/01/202201:00:00 PM95Very Low501:00:00 PMVery Low
1612/01/202202:00:00 PM65Very Low502:00:00 PMVery Low
1712/01/202203:00:00 PM55Very Low503:00:00 PMVery Low
1812/01/202204:00:00 PM95Very Low504:00:00 PMVery Low
1912/01/202205:00:00 PM95Very Low505:00:00 PMVery Low
2012/01/202206:00:00 PM55Low506:00:00 PMLow
2112/01/202207:00:00 PM45Low507:00:00 PMLow
2212/01/202208:00:00 PM35Low508:00:00 PMLow
2312/01/202209:00:00 PM15Low509:00:00 PMLow
2412/01/202210:00:00 PM25Medium510:00:00 PMMedium
2512/01/202211:00:00 PM25Medium511:00:00 PMMedium
2612/02/202212:00:00 AM26Medium612:00:00 AMMedium
2712/02/202201:00:00 AM26Medium601:00:00 AMMedium
2812/02/202202:00:00 AM36Medium602:00:00 AMMedium
2912/02/202203:00:00 AM46Medium603:00:00 AMMedium
3012/02/202204:00:00 AM26Medium604:00:00 AMMedium
3112/02/202205:00:00 AM46Medium605:00:00 AMMedium
3212/02/202206:00:00 AM16Medium606:00:00 AMMedium
3312/02/202207:00:00 AM76Medium607:00:00 AMMedium
3412/02/202208:00:00 AM106Medium608:00:00 AMMedium
3512/02/202209:00:00 AM86Very Low609:00:00 AMVery Low
3612/02/202210:00:00 AM76Very Low610:00:00 AMVery Low
3712/02/202211:00:00 AM66Very Low611:00:00 AMVery Low
3812/02/202212:00:00 PM76Very Low612:00:00 PMVery Low
3912/02/202201:00:00 PM76Very Low601:00:00 PMVery Low
4012/02/202202:00:00 PM56Very Low602:00:00 PMVery Low
4112/02/202203:00:00 PM86Very Low603:00:00 PMVery Low
4212/02/202204:00:00 PM96Very Low604:00:00 PMVery Low
4312/02/202205:00:00 PM96Very Low605:00:00 PMVery Low
4412/02/202206:00:00 PM66Low606:00:00 PMLow
4512/02/202207:00:00 PM26Low607:00:00 PMLow
4612/02/202208:00:00 PM16Low608:00:00 PMLow
4712/02/202209:00:00 PM16Low609:00:00 PMLow
4812/02/202210:00:00 PM26High610:00:00 PMHigh
4912/02/202211:00:00 PM16High611:00:00 PMHigh
5012/03/202212:00:00 AM27High712:00:00 AMHigh
5112/03/202201:00:00 AM67High701:00:00 AMHigh
5212/03/202202:00:00 AM27High702:00:00 AMHigh
5312/03/202203:00:00 AM17High703:00:00 AMHigh
5412/03/202204:00:00 AM17High704:00:00 AMHigh
5512/03/202205:00:00 AM47High705:00:00 AMHigh
5612/03/202206:00:00 AM47High706:00:00 AMHigh
5712/03/202207:00:00 AM47High707:00:00 AMHigh
5812/03/202208:00:00 AM77High708:00:00 AMHigh
5912/03/202209:00:00 AM27Medium709:00:00 AMMedium
6012/03/202210:00:00 AM27Medium710:00:00 AMMedium
6112/03/202211:00:00 AM27Medium711:00:00 AMMedium
6212/03/202212:00:00 PM47Medium712:00:00 PMMedium
6312/03/202201:00:00 PM17Medium701:00:00 PMMedium
6412/03/202202:00:00 PM57Medium702:00:00 PMMedium
6512/03/202203:00:00 PM57Medium703:00:00 PMMedium
6612/03/202204:00:00 PM97Medium704:00:00 PMMedium
6712/03/202205:00:00 PM97High705:00:00 PMHigh
6812/03/202206:00:00 PM57High706:00:00 PMHigh
6912/03/202207:00:00 PM47High707:00:00 PMHigh
7012/03/202208:00:00 PM37High708:00:00 PMHigh
7112/03/202209:00:00 PM17High709:00:00 PMHigh
7212/03/202210:00:00 PM27Very High710:00:00 PMVery High
7312/03/202211:00:00 PM27Very High711:00:00 PMVery High
Sheet1
Cell Formulas
RangeFormula
E2:E73E2=WEEKDAY([@Date])
F2:F73F2=XLOOKUP([@[Time Of Day]]&[@Weekday],tblRates[Time]&tblRates[Weekday],tblRates[TariffType])

The trick is to combine the two parameters needed into one.

That said, no reason that you couldn't do the same with Power Query (Data Model not really needed).
Using Power Query bring in the Tariff Table and add a Weekday-Time column:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="tblRates"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Weekday", Int64.Type}, {"Time", type time}, {"TariffType", type text}}),
    AddedWeekdayTime = Table.AddColumn(ChangedType, "Weekday-Time", each Text.From( [Weekday] ) & "-" & Text.From( [Time] )),
    ChangedType1 = Table.TransformColumnTypes(AddedWeekdayTime,{{"Weekday-Time", type text}})
in
    ChangedType1
Same with the Times table, and then merge them:
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="tblTimes"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time Of Day", type time}, {"Hours Used", Int64.Type}, {"Weekday", Int64.Type}, {"TarrifType", type text}}),
    AddedWeekdayTime = Table.AddColumn(ChangedType, "Weekday-Time", each Text.From( [Weekday] ) & "-" & Text.From( [Time Of Day] )),
    ChangedType1 = Table.TransformColumnTypes(AddedWeekdayTime,{{"Weekday-Time", type text}}),
    MergedtblRates = Table.NestedJoin(ChangedType1, {"Weekday-Time"}, tblRates, {"Weekday-Time"}, "tblRates", JoinKind.LeftOuter),
    ExpandedTariffType = Table.ExpandTableColumn(MergedtblRates, "tblRates", {"TariffType"}, {"TariffType"})
in
    ExpandedTariffType
Certainly more applicable if the Times table is updating regularly.
 
Upvote 0
Solution
Lovely, the example above is only a snippet so i have thousands and thousands of lines in the tables but that worked a treat. i'll have a play.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Extract Month numbers from years and remove zero as well
Replies
2
Views
663
hananak
H
S
  • Solved
Sum for specific month
Replies
3
Views
353
Fluff
Fluff
R
  • Question Question
Open file copy and paste when value changes
Replies
6
Views
514
Alex Blakenburg
A
R
  • Question Question
Make a count of records with a function in the same table but filtered by a field in Current record ?
Replies
2
Views
920
RasmusKA
R

Forum statistics

Threads
1,224,885
Messages
6,181,583
Members
453,055
Latest member
cope7895

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