Forum Discussion

towardhuzzahmo's avatar
towardhuzzahmo
Frequent Visitor
2 years ago

Need Urgent Help W/ Power Query for HR Analysis

Hi, I'm a college student currently working on a non-school related project using Excel, Power BI, and Power Query. I've got a dataset I'm trying to make sense of and could really use some advice on...
Need Help
dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi I've tried to create something with sample data, but I'm not sure if I understand what do you exactly need.

(I've added 2 more rows to your sample data).

v1

You can check it by creating blank query and replacing whole code with this one:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY/BDsIgDIZfhXBewiCxwFETX8DrsgMqkkXClrHF+PYWh4lznnQNpT8FvvSvKlpigKYFPVg3etOj6jCjCYMh3oQzHi6YnAnGtQKUuHbenK5YRck3WAiti59QZM6CFVkSi2YCHyQ9MRVIOWMmxLG9+SY44nprQ+4Be/5L3u424r6NjQlLw6sAYbqHt0l1igV4sHHIUrFs7MXbj33b2S8z/ov6mK5+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Regular/Temporary" = _t, #"Full Time/Part Time" = _t, Location = _t, Sex = _t, #"Last Start" = _t, #"Manager#(lf)#(lf)(only mark yes if true)" = _t, Ethnicity = _t, #"Fiscal Year" = _t, #"Seperation Date" = _t]),
    CleanTrimColumnNames = Table.TransformColumnNames(Source, each Text.Trim(Text.Clean(_))),
    ChangedTypeUS = Table.TransformColumnTypes(CleanTrimColumnNames,{{"Last Start", type date}, {"Fiscal Year", Int64.Type}, {"Seperation Date", type date}}, "en-US"),
    Ad_FiscalYearsAsColumns = List.Accumulate(
    List.Sort(List.Distinct(ChangedTypeUS[Fiscal Year])),
    ChangedTypeUS,
    (s,c)=> Table.AddColumn(s, Text.From(c), each if Date.Year([Last Start]) <= Date.Year(#date(Number.From(c), 1, 1)) then 1 else 0, Int64.Type)
),
    #"Reordered Columns" = Table.ReorderColumns(Ad_FiscalYearsAsColumns,{"Employee ID", "Last Start", "2015", "2016", "2017", "Regular/Temporary", "Full Time/Part Time", "Location", "Sex", "Manager(only mark yes if true)", "Ethnicity", "Fiscal Year", "Seperation Date"})
in
    #"Reordered Columns"

 

v2

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY/BDsIgDIZfhXBewiCxwFETX8DrsgMqkkXClrHF+PYWh4lznnQNpT8FvvSvKlpigKYFPVg3etOj6jCjCYMh3oQzHi6YnAnGtQKUuHbenK5YRck3WAiti59QZM6CFVkSi2YCHyQ9MRVIOWMmxLG9+SY44nprQ+4Be/5L3u424r6NjQlLw6sAYbqHt0l1igV4sHHIUrFs7MXbj33b2S8z/ov6mK5+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Regular/Temporary" = _t, #"Full Time/Part Time" = _t, Location = _t, Sex = _t, #"Last Start" = _t, #"Manager#(lf)#(lf)(only mark yes if true)" = _t, Ethnicity = _t, #"Fiscal Year" = _t, #"Seperation Date" = _t]),
    CleanTrimColumnNames = Table.TransformColumnNames(Source, each Text.Trim(Text.Clean(_))),
    ChangedTypeUS = Table.TransformColumnTypes(CleanTrimColumnNames,{{"Last Start", type date}, {"Fiscal Year", Int64.Type}, {"Seperation Date", type date}}, "en-US"),
    FiscalYears = List.Buffer(List.Transform(List.Sort(List.Distinct(ChangedTypeUS[Fiscal Year])), Text.From)),
    StepBack = ChangedTypeUS,
    Ad_FiscalYearsAsColumns = 
        List.Accumulate(
            FiscalYears,
            StepBack,
            (s,c)=> Table.AddColumn(s, c, each if Date.Year([Last Start]) <= Date.Year(#date(Number.From(c), 1, 1)) then 1 else 0, Int64.Type)
        ),
    #"Grouped Rows" = Table.Group(Ad_FiscalYearsAsColumns, {"Employee ID"}, {{"Details", each _}}),
    Result = 
        List.Accumulate(
            FiscalYears,
            #"Grouped Rows",
            (s,c)=> Table.AddColumn(s, c, each Record.Field(Table.Max(Table.SelectColumns([Details], c), c), c), Int64.Type)
        )
in
    Result

 

towardhuzzahmo's avatar
towardhuzzahmo
Frequent Visitor
2 years ago

hi dufoq3 , 

 

Thank you so much for reading and responding to my post!!!! You are defintly on the right track!!!!! I should have includeded an example of the table I was envisioning as the end product. I essentially, want to condense all of the repeat Employee ID's which is why i was thinking of using the Group By function. I dont necassarily want to treat the repeating ID's as duplicates because they arent, they are unique records for a different fiscal year.

 

Mock of what I think I want:

*note (my real data spans from 2015-2023)

 

Employee IDRegular/TemporaryFull Time/Part TimeLocationSexLast Start

Manager

(only mark yes if true)

EthnicitySeperation Date201520162017201820192020202120222023
000069Regularpsanta landf1/2/1986 Black9/21/2017111000000
008677Regularfbowling greenf6/1/2015yesAsian6/16/2017110000000

 

 

I want, whatever the final product is, to be able to be used, to make pivot tables and graphs, maybe even an HR dashboard. Basically, I know I cant do that with the current way my data is structured. 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    If you want me to edit the code - len me know the conditions i.e. Fiscal Year starts 1st of October. Seperation date must be greather than.... etc.

    • towardhuzzahmo's avatar
      towardhuzzahmo
      Frequent Visitor
      2 years ago

      Hi, 

       

      I would love if you could edit the code. That would be very helpful 🙂 The fiscal year runs from July 1 to June 30. I'm not quite sure what the seperation date should be greater than. But I think, it would be greater than July 1, 2015. If there are any other conditions that would be helpful for me to share, let me know. Thank you, thank you!

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Try this one. Edit 2nd step YourSource = Source and refer your data.

        Logic: if employee worked at least 1 day in fiscal year then then 1 else 0.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY/BDsIgDIZfhXBewjZdB0dNfAGvCwdUXBYJW2CL8e0tQozLPOkaSn+gfPnbNDTHAEEzetTtZJRDNWB6ZUdFjLIXPFwxC1ayQnBAiWtv1PmGtcyLCguhMvsJReYsWJFVYxGsxIagI5NDXc+YAXHq76azLWmd1jbdAXv9C7M9tMd95ztllwOvAoT4Dh9ORYgFeNR+THIbefzNO0yuH/QXj/+igruKbVKPlE8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"Regular/Temporary" = _t, #"Full Time/Part Time" = _t, Location = _t, Sex = _t, #"Last Start" = _t, #"Manager#(lf)#(lf)(only mark yes if true)" = _t, Ethnicity = _t, #"Fiscal Year" = _t, #"Seperation Date" = _t]),
    YourSource = Source,
    CleanTrimColumnNames = Table.TransformColumnNames(YourSource, each Text.Trim(Text.Clean(_))),
    ChangedTypeUS = Table.TransformColumnTypes(CleanTrimColumnNames,{{"Last Start", type date}, {"Fiscal Year", Int64.Type}, {"Seperation Date", type date}}, "en-US"),
    FiscalYears = List.Buffer(List.Transform(List.Sort(List.Distinct(ChangedTypeUS[Fiscal Year])), Text.From)),
    StepBack = ChangedTypeUS,
    Ad_FiscalYearsAsColumns = 
        List.Accumulate(
            FiscalYears,
            StepBack,
            (s,c)=> Table.AddColumn(s, c, each 
                if Date.Year([Last Start]) < Number.From(c) and ( [Seperation Date] = null or Date.Year([Seperation Date]) > Number.From(c) ) then 1 else
                if [Last Start] >= #date(Number.From(c), 7, 1) or ( [Seperation Date] = null or [Seperation Date] >= #date(Number.From(c), 7, 1) ) then 1 else 0 )
        ),
    GroupedRows = Table.Group(Ad_FiscalYearsAsColumns, {"Employee ID"}, {{"Details", each _, type table}}),
    Ad_FiscalYearsAsColumns2 = 
        List.Accumulate(
            FiscalYears,
            GroupedRows,
            (s,c)=> Table.AddColumn(s, c, each Record.Field(Table.Last(Table.SelectColumns([Details], c)), c), Int64.Type)
        ),
    DetailsLastRow = Table.TransformColumns(Ad_FiscalYearsAsColumns2, {{"Details", Table.Last}}),
    ExpandedDetails = Table.ExpandRecordColumn(DetailsLastRow, "Details", {"Regular/Temporary", "Full Time/Part Time", "Location", "Sex", "Last Start", "Manager(only mark yes if true)", "Ethnicity", "Seperation Date"}, {"Regular/Temporary", "Full Time/Part Time", "Location", "Sex", "Last Start", "Manager(only mark yes if true)", "Ethnicity", "Seperation Date"})
in
    ExpandedDetails