Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bkr
Helper I
Helper I

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 (organisational unit,line manager, pay grade) in a fact table.

 

Now: How can I get entry and exit date into a dimension table with one row for each employee from an Excel table that has one entry per employee and month?

 

Source:

IDEmployee Month/year  gender  org. unit  line manager  pay grade
1John Doe03-2023male  
1John Doe04-2023male   
1John Doe05-2023male   
1John Doe06-2023male   
1John Doe07-2023male   
2Melissa Smith 04-2023female   
2Melissa Smith 05-2023female   
2Melissa Smith 06-2023female   
2Melissa Smith 07-2023female   
2Melissa Smith 08-2023female   
3Tim Ding08-2023male   

 

Desired Dimension table output:

IDEmployeeEntryLeavegender
1John Doe 03-2023  07-2023  male
2Melissa Smith  04-2023  08-2023  female
3Tim Ding 08-2023  08-2023  male

 

I could get either entry or leave using GroupBy but not both.
I could duplicate the data, do GroupBy in each and then recombine them or put the months in the fact table and evaluate using DAX, but I believe there is a better way to do it in PowerQuery. Any idea?
Thanks!

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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"

View solution in original post

3 REPLIES 3
Ahmedx
Super User
Super User

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
Super User
Super User

to know how to solve the problem watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26Rinnjq-TEMZIPsnG3?e=ypeziK

 

I cannot access it unfortunately. Access to onedrives is blocked by my employer

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.