I have a power query table including employees salaries/employee number/job title/net pay/total and the month.
We have calculated YTD totals for each category and created a cell variable that allows all data to be refreshed.
If we have an ex-employee that isn't paid in June, when the 'Filtered Rows' by Month is applied, they disappear off the table and their YTD amount is not visible.
How can we still have our ex employees information and year to date totals in the table, just the 'current month' view pays as £0. We are able to do this in formulas on Excel, but I am sure there is a way to do this on Power Query.
let
Source = Excel.CurrentWorkbook(){[Name="PayrollQuery"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month-Year", type datetime}, {"Employee Number", Int64.Type}, {"Employee", type text}, {"Net Pay", type number}, {"Net Pay less expenses", type number}, {"Employee NI", type number}, {"Employer NI", type number}, {"PAYE", type number}, {"Expenses", type number}, {"Pensions", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Employee", "Net Pay"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Net Pay less expenses", "Net Pay"}}),
#"DateType" = Table.TransformColumnTypes(#"Renamed Columns",{{"Month-Year", type date}}),
#"Full Name" = Table.AddColumn(#"DateType", "Full Name", each Record.Field( EmpMapQuery{[Employee Number=[Employee Number]]}, "Full Name")),
#"Added Custom2" = Table.AddColumn(#"Full Name", "Division", each EmpMapQuery[Professional]{List.PositionOf(EmpMapQuery[Employee Number], [Employee Number])}),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Division", "Professional"}}),
#"Department" = Table.AddColumn(#"Renamed Columns1", "Department", each Record.Field(EmpMapQuery{[Employee Number=[Employee Number]]}, "Department")),
#"Job Title" = Table.AddColumn(#"Department", "Job Title", each Record.Field(EmpMapQuery{[Employee Number=[Employee Number]]}, "Job Title")),
#"Status" = Table.AddColumn(#"Job Title", "Status", each Record.Field(EmpMapQuery{[Employee Number=[Employee Number]]}, "Status")),
#"Annual Salary" = Table.AddColumn(#"Status", "Annual Salary", each Record.Field(EmpMapQuery{[Employee Number=[Employee Number]]}, "Annual Salary")),
#"Changed Type2" = Table.TransformColumnTypes(#"Annual Salary",{{"Annual Salary", Currency.Type}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"Annual Salary", Currency.Type}, {"Net Pay", Currency.Type}, {"Employee NI", Currency.Type}, {"Employer NI", Currency.Type}, {"PAYE", Currency.Type}, {"Expenses", Currency.Type}, {"Pensions", Currency.Type}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Changed Type3",{{"Total", Currency.Type}}),
#"Month Value" = Table.AddColumn(#"Changed Type4", "Month", each Record.Field(Table15{[Month = [#"Month-Year"]]}, "Value")),
YTD = Table.AddColumn(#"Month Value", "Total Year To Date", (x)=> List.Sum(Table.SelectRows(#"Month Value", each _[Employee Number] = x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [Total])),
NetPayYTD = Table.AddColumn(#"YTD", "Net Pay YTD", (x)=> List.Sum(Table.SelectRows(#"YTD", each _[Employee Number]= x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [Net Pay])),
EmployeeYTD = Table.AddColumn(#"NetPayYTD", "Employee NI YTD", (x)=> List.Sum(Table.SelectRows(#"NetPayYTD", each _[Employee Number]= x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [Employee NI])),
EmployerYTD = Table.AddColumn(#"EmployeeYTD", "Employer NI YTD", (x)=> List.Sum(Table.SelectRows(#"EmployeeYTD", each _[Employee Number] = x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [Employer NI])),
PAYEYTD = Table.AddColumn(#"EmployerYTD", "PAYE YTD", (x)=> List.Sum(Table.SelectRows(#"EmployerYTD", each _[Employee Number]= x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [PAYE])),
ExpensesYTD = Table.AddColumn(#"PAYEYTD", "Expenses YTD", (x)=> List.Sum(Table.SelectRows(#"PAYEYTD", each _[Employee Number]= x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [Expenses])),
PensionsYTD = Table.AddColumn(#"ExpensesYTD", "Pensions YTD", (x)=> List.Sum(Table.SelectRows(#"ExpensesYTD", each _[Employee Number]= x[Employee Number] and _[#"Month-Year"]<=x[#"Month-Year"]) [Pensions])),
#"Reordered Columns" = Table.ReorderColumns(PensionsYTD,{"Month", "Month-Year", "Employee Number", "Full Name", "Department", "Job Title", "Professional", "Status", "Annual Salary", "Net Pay", "Employee NI", "Employer NI", "PAYE", "Expenses", "Pensions", "Total", "Net Pay YTD", "Employee NI YTD", "Employer NI YTD", "PAYE YTD", "Expenses YTD", "Pensions YTD", "Total Year To Date"}),
#"CurrencyType" = Table.TransformColumnTypes(#"Reordered Columns",{{"Total Year To Date", Currency.Type}, {"Net Pay YTD", Currency.Type}, {"Employee NI YTD", Currency.Type}, {"Employer NI YTD", Currency.Type}, {"PAYE YTD", Currency.Type}, {"Expenses YTD", Currency.Type}, {"Pensions YTD", Currency.Type}}),
#"Filter Function" = Table.SelectRows(CurrencyType, each [#"Month-Year"] = FilterDate or [Net Pay] <= 0),
#"Removed Columns1" = Table.RemoveColumns(#"Filter Function",{"Month"})
in
#"Removed Columns1"
I have attached the advanced editor formulas above.
