Hi,
I am new to the joys of Power Query so this might be easy, I just don't know how to ask the question correctly..
I have attached a spreadsheet which hopefully illustrates the issue.
This is based on a timesheet dump that is taken from another system. The raw data imported into excel is now over 50,000 rows and causing an incredibly slow spreadsheet (with other calculations).
I want to:
Remove certain rows - For example (PTO, Public Holiday)
Remove duplicate entries based on date, level codes and value. The reports that get are pulled from the timesheet system might have an overlapping date range.
Summarise the entries for a project for an employee per month rather that having the daily entries resulting in a much smaller dataset to use in another system..
e.g. Employee 1 Project 1 March 2023 10 hours,
Employee 1 Project 2 March 2023 5 hours
Thank you all for any assistance you can offer.
Kind regards
Steve
Based upon the info provided then:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([PAY_CODE] <> "PTO" and [PAY_CODE] <> "PUBLICHOLIDAY")),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"EVENT_DATE", "LEVEL1_CODE", "VALUE"}),
#"Extracted Month" = Table.TransformColumns(#"Removed Duplicates",{{"EVENT_DATE", Date.Month, Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Extracted Month", {"EMP_LAST_FIRST_ID", "EVENT_DATE", "LEVEL1_CODE"}, {{"Total", each List.Sum([VALUE]), type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"EVENT_DATE", "Month"}})
in
#"Renamed Columns"
Hi Alan,
Thank you ever so much - that looks pretty much spot on. And I can understand the coding so will be able to tweak it.
The only thing I noticed running it against the table was that for Employee 1 on Project 1 the figures in Table 1:
For January 2023 the Timedump data totals 25.75 whereas the query result shows 24.75
For February 2023 the Timedump data totals 19.75 whereas the query result shows 18.75
The other entries seem to be correct. Have I missed something obvious in applying the query?
Kind regards
Steve
