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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.