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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors