Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Filter out most recent date user level in PowerQuery

Hi Everybody,   I am looking for a solution to filter out or mark the most recent date in from a salary registration export. table 1 shows an example of the dataset, which in reality contains thous...
Need Help
DOLEARY85's avatar
DOLEARY85
Resident Rockstar
3 years ago

Hi,

 

hopefully this helps, m - query:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY9LDoAgDESvYliTMG1BwVO4J9z/GsJK+QgmndWbTqcxqosJ8EorygIZIsO0ASegku55HuYRhw6PQ2YJR5sgzrv6giz4dH+R338ogewfrr8btI6+wyBDJi0KtxXfHUthWewMwutCugE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee_ID = _t, FTE = _t, #"Start date Salary" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee_ID", type text}, {"Start date Salary", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee_ID"}, {{"Maxdate", each List.Max([Start date Salary]), type nullable datetime}, {"Details", each _, type table [Employee_ID=nullable text, FTE=nullable number, Start date Salary=nullable datetime]}}),
#"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"FTE", "Start date Salary"}, {"Details.FTE", "Details.Start date Salary"}),
#"Added Custom" = Table.AddColumn(#"Expanded Details", "Custom", each if [Details.Start date Salary] =[Maxdate] then 1 else 0),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Flag"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Flag] = 1))
in
#"Filtered Rows"

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍