Forum Discussion

bkr's avatar
bkr
Icon for Helper I rankHelper I
2 years ago
Solved

Get Min and Max for specific entries in Power Query

Dear community,   I have an employee list that is updated every month. Static stuff (employee ID, Name, Company entry and exit, gender etc.) are stored in a Dimension table and variable stuff (orga...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    try this code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8hTcMlPBTIv7Lmw4WKDnpExkJ2bmJOqFKuDqWbDhf0E1QDNubCTgJodF/su7CVCzW40NUZAjm9qTmZxcaJCcG5mScahBWjOSkslpBjJfYQVIzmUOMW7iVW84cKmC5sxFIO4IZm5Ci6ZeeloqiBqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D = _t, #"Employee " = _t, #"Month/year " = _t, #" gender " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D", Int64.Type}, {"Employee ", type text}, {"Month/year ", type date}, {" gender ", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"D", "Employee ", " gender "}, {{"min", each List.Min([#"Month/year "]), type nullable date}, {"max", each List.Max([#"Month/year "]), type nullable date}})
in
    #"Grouped Rows"
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8hTcMlPBTIv7Lmw4WKDnpExkJ2bmJOqFKuDqWbDhf0E1QDNubCTgJodF/su7CVCzW40NUZAjm9qTmZxcaJCcG5mScahBWjOSkslpBjJfYQVIzmUOMW7iVW84cKmC5sxFIO4IZm5Ci6ZeeloqiBqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [D = _t, #"Employee " = _t, #"Month/year " = _t, #" gender " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"D", Int64.Type}, {"Employee ", type text}, {"Month/year ", type date}, {" gender ", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"D", "Employee ", " gender "}, {{"min", each List.Min([#"Month/year "]), type nullable date}, {"max", each List.Max([#"Month/year "]), type nullable date}})
in
    #"Grouped Rows"