Forum Discussion
Helper IIIGrouping or Filtering Data of one column based on another
If you Group By on workweek and Job with 2 aggregations: Max of Filedate , All Rows.
You can then expand the 'all' column.
Then Filter the table to keep the rows where the filedate = MaxFiledate (that bit sounds complicated but you can filter from the column header of MaxFileDate -> just choose one date from the dropdown -> then replace the hardcoded date in the formula bar with the name of the filedate column)
--
let me know how it goes.
Community ChampionThe line for
#"Expanded All Rows" = Table.Expand....is only returning the Amount from the original data. It needs to return FileDate as well.
The idea behind the algorithm is that the data at this stage is back to the original dataset but with an additional column MaxFileDate which is the max for each workweek/job combination.
--
Then the table gets filtered to retain only the rows where Maxdate = filedate. That's the last part of the algorithm in my previous post. This will be a Table.SelectRows statement. You can write it yourself or get Power Query to write it for a hardcoded value which you can then edit with the name of the column.
Helper IIIThat worked perfectly, thank you!