Log In
Register
Home
Active Member
May 6, 2021
Offline
Hi All,
I'd like to pick your brains if I could?
I may be over complicating this - if so, please tell me as I am relatively new to Power Query.
I have annual leave data (hours) coming straight off the OLAP Cube from peoples timesheets but would like to incorporate the persons annual allowance and have it reduce automatically on a exported pivot table.
Many thanks in advance
Andy
See Answer
Trusted Members
February 13, 2021
Offline
If I understand you correctly you want something like this:
let
Source = Table.NestedJoin(Leave, {"Name"}, Allowance, {"Name"}, "Allowance", JoinKind.LeftOuter),
#"Expanded Allowance" = Table.ExpandTableColumn(Source, "Allowance", {"Allowance"}, {"Allowance.1"}),
// Group all rows to add index column per employee
#"Grouped Rows" = Table.Group(#"Expanded Allowance", {"Name"}, {{"Group", each _, type table [Name=nullable text, Date=nullable date, Hours=nullable number, Allowance.1=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Group],"Index",1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Group"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Hours", "Allowance.1", "Index"}, {"Date", "Hours", "Allowance.1", "Index"}),
// Second index column is to force position of the list to get an accurate remaining allowance by employee
#"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 0, 1, Int64.Type),
// Running total of hours
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(if [Index]=1 then List.Range(#"Added Index"[Hours],[Index.1],[Index]) else List.Range(#"Added Index"[Hours],[Index.1]-([Index]-1),[Index]))),
#"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [Allowance.1] - [Custom]),
// Column to account for new allowances
#"Added Conditional Column" = Table.AddColumn(#"Inserted Subtraction", "Allowance", each if [Index] = 1 then [Allowance.1] else #"Inserted Subtraction"[Subtraction]{[Index.1]-1}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Name", "Date", "Hours", "Allowance", "Subtraction"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Subtraction", "Adjusted Allowance"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Name", type text}, {"Date", type date}, {"Hours", Int64.Type}, {"Allowance", Int64.Type}, {"Adjusted Allowance", Int64.Type}})
in
#"Changed Type"
Answers Post
Moderators
January 31, 2022
Offline
As a variant, this code will produce a summary by person (Name, Allowance, Leave Taken and Balance).
let
Source = Excel.CurrentWorkbook(){[Name="Allowance"]}[Content],
Merge = Table.NestedJoin(Source, {"Name"}, Leave, {"Name"}, "Leave", JoinKind.LeftOuter),
AddedCustom = Table.AddColumn(Merge, "Leave Taken", each List.Sum ( Table.Column ( [Leave], "Hours" ) )),
Subtract = Table.AddColumn(AddedCustom, "Balance", each [Allowance] - [Leave Taken], type number),
Remove = Table.RemoveColumns(Subtract,{"Leave"})
in
Remove
Active Member
May 6, 2021
Offline
Was this created in 365? Tried to step it through in edit and got errors. We are currently still using 2019.
Andy
Trusted Members
February 13, 2021
Offline
Mine was, yes. With that error the just take out the last arguement.
#"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 0, 1, Int64.Type),
That is just assigning the type whole number and not essential to setting up the index column.
1 Guest(s)
