Mcode not working to calculate correct Availabe Hrs

Power Query
1

Hi @Melissa

I am trying to work out the correct available Hrs in TAvailableHrs Column of the EMALLCompanyAvailableHrs table in the attached PBIX file but coming with the wrong answer.

Here is the mcode

image
image1141Ă—101 37.8 KB

Could you please advise where I am going wrong?

Requirement

_ If the transdate is between the CustPreviousworkinghoursstartdate and CustPreviousworkinghoursenddate I want it to give me the CustAvailableHrsPerday Column in the AllRows column if not give me the Available Hours Column.

Your guidance will be very much appreciated. Thanks

P
tAvailableHrs Scenario.pbix (189.0 KB)

2

Hi @ambepat,

Will need the data as well…

3

Hi @Melissa

Here you are. I have already done transformations in the PBIX file here:
tAvailableHrs Scenario.pbix (189.0 KB)

Thanks

Patrick N

4

Hi @Melissa if you want the excel files as well here is everything.

tAvailableHrs Scenario.pbix (202.3 KB)
Employees_CustWorkinghoursperweek.xlsx (12.3 KB)
EMAllCompany.xlsx (15.2 KB)

Thanks again for your help

1 Like
5

Hi @ambepat,

Thanks for supplying the data :+1:
First you can avoid “data” errors by using a technique illustrated here.

Review this chapter in the PQ course for related content, start around the 6:50 min mark.

Here’s your sample file.
tAvailableHrs Scenario.pbix (204.9 KB)

I hope this is helpful

6

Hi @Melissa,

Thanks for this but on checking it does not seem to give the correct number I am looking for. If you look at this screenshot I have filtered to employee number 127036. Where I have circled in red the trans date of 01/07/2021 is between Custprev start date and end date so I am expecting 6.4 hrs in TAvailbleHrs Column and not 8hrs. Anything beyond the 06/09/2021 gets the available hours.

image
image973Ă—926 145 KB

Hope it is clear and thanks again for helping me.

P

7

Thanks!
I’ll look into that and get back to you

8

Oh dear, I referenced [AllRows]{0}[CustAvailableHrsPerDay] instead of
myT{0}[CustAvailableHrsPerDay]
:crazy_face:

Table.AddColumn( #"Merged Queries", "TAvailableHrs", each 
    if [AllRows] = null then [Available Hours]
    else
      let
        tDate = [TransDate], 
        myT = Table.SelectRows( [AllRows], (IT) => ( tDate >= IT[CustPreviousworkinghoursstartdate] and tDate <= IT[CustPreviousworkinghoursenddate] ) )
      in
        if Table.RowCount(myT) = 0 then [Available Hours] else myT{0}[CustAvailableHrsPerDay]
)
9

Thanks for this Melissa. Working now. My brain is exploding with all this M language but learning lots. All good.

10

Hello @ambepat just following up if you were able to solve your inquiry with @Melissa 's suggestion?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.