Excel Power Query Max number

[kaz123]

Hi

I have the below Power Query that helps me group absences for each employee. I wanted to find out how to limit the Total Days value (highlighted below) to 262.

= Table.Group(Source, {"Absence Type", "Employee name", "Employee Type", "Employee ID", "Department"}, {{"Total Days", each List.Sum([Total Days]), type number}, {"Excluding Days", each List.Sum([Excluding Days]), type number}})


Thanks

 

[JGordon11]

Replace List.Sum([Total Days]) with List.Min({List.Sum([Total Days]),262})

 

[kaz123]

Replace List.Sum([Total Days]) with List.Min({List.Sum([Total Days]),262})

Thanks, this limits that field to 262 when it exceeds that number. The only other question I had was if its possible to reference a lookup table within the power query? This is because the next field 'Excluding Days' should be "Total Days" value minus the allowance value based on the "Absence Type" which is held on the same workbook as a named range.

Absence Type	Allowance
Sick	20
Medical	10
Study Leave	15

So if the "Total Days" value is limited to 262, the "Excluding Days" should be 242

 

[JGordon11]

Yes - bring the lookup table (change the name in quotes to your table name) into your query with a step like like:

tblLU = Excel.CurrentWorkbook(){[Name="Lookup Table Name"]}[Content]

then to get the allowance you would use something like:

= Table.Group(Source, {"Absence Type", "Employee name", "Employee Type", "Employee ID", "Department"}, let LS = List.Min({List.Sum([Total Days]),262}) in {{"Total Days", each LS, type number}, {"Excluding Days", each LS - tblLU{[Absence Type = _[Absence Type]]}[Allowance], type number}})

might work, I didn't test it.

 

[kaz123]

Yes - bring the lookup table (change the name in quotes to your table name) into your query with a step like like:

tblLU = Excel.CurrentWorkbook(){[Name="Lookup Table Name"]}[Content]

then to get the allowance you would use something like:

= Table.Group(Source, {"Absence Type", "Employee name", "Employee Type", "Employee ID", "Department"}, let LS = List.Min({List.Sum([Total Days]),262}) in {{"Total Days", each LS, type number}, {"Excluding Days", each LS - tblLU{[Absence Type = _[Absence Type]]}[Allowance], type number}})

might work, I didn't test it.

Thanks for the above, I am getting the error message below:

An error occurred in the ‘’ query. Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

My full query is as below:

= let

tblLU = Excel.CurrentWorkbook(){[Name=“absence_table”]}[Content],

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Group = Table.Group(Source, {"Absence Type", “Employee name", “Employee Type", “Employee ID", “Department”}, let LS = List.Min({List.Sum([Total Days]),262}) in {{"Total Days", each LS, type number}, {"Excluding Days", each LS - tblLU{[Absence Type = _[Absence Type]]}[Allowance], type number}})

in
Group

 

[JGordon11]

What happens if you use this instead:

Group = Table.Group(Source, {"Absence Type", “Employee name", “Employee Type", “Employee ID", “Department”}, let LS = List.Min({List.Sum([Total Days]),262}) in {{"Total Days", each LS, type number}, {"Excluding Days", each LS - tblLU{tblLU[Absence Type = _[Absence Type]]}[Allowance], type number}})

if it still errors out then if you can include representative sample of your Source table I may be able to debug it

 

[kaz123]

What happens if you use this instead:

Group = Table.Group(Source, {"Absence Type", “Employee name", “Employee Type", “Employee ID", “Department”}, let LS = List.Min({List.Sum([Total Days]),262}) in {{"Total Days", each LS, type number}, {"Excluding Days", each LS - tblLU{tblLU[Absence Type = _[Absence Type]]}[Allowance], type number}})

if it still errors out then if you can include representative sample of your Source table I may be able to debug it

Sorry for the late reply, I have attached an image showing the source data and what the final data should look like after running the power query. The only issue I have is the "Excluding Days" field should be "Total Days" minus the allowance for the absence type on the lookup table

 

[JGordon11]

try

let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    tblLU = Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content],
    Group = Table.Group(Source, {"Absence Type", "Employee Name", "Employee Type", "Employee ID", "Department"}, 
         {{"Total Days", each let LS = List.Min({List.Sum([Total Days]),262}) in LS, type number}}),
    Result = Table.AddColumn(Group,"Excluding Days", each [Total Days] - tblLU{[Absence Type = _[Absence Type]]}[Allowance])

in
    Result

 

[kaz123]

try

let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    tblLU = Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content],
    Group = Table.Group(Source, {"Absence Type", "Employee Name", "Employee Type", "Employee ID", "Department"},
         {{"Total Days", each let LS = List.Min({List.Sum([Total Days]),262}) in LS, type number}}),
    Result = Table.AddColumn(Group,"Excluding Days", each [Total Days] - tblLU{[Absence Type = _[Absence Type]]}[Allowance])

in
    Result

Thank you , this works