Unpivoting data based on dates

[perryn27]

Hi everyone!

I have a table with employee data from which I would like to create a new table stating employee information by date:

The first table has each employee as a row with details like name, division, pay rate, as well as their hire date and termination date.

In the second table I would like to have an "unpivoted version", where I have every date (since the first employee was hired until today), and every employee with their information against that date and whether they were employed on that date or not. i.e. each date is a unique employee:date combination.

I have attached 2 excel files as examples of what I have and would like to have.

I have tried unpivoting and creating new tables that summarise information and so far haven't got anywhere so any help would be much appreciated!

Thanks

Table 1

Name	Pay Rate	Division	Hire Date	Termination Date
Adam	30000	Marketing	11/01/2018	30/09/2020
Ben	32000	Sales	18/06/2019
Christine	34000	Finance	12/12/2022

Table 2

Date	Name	Pay Rate	Division	Employed?
11/01/2018	Adam	30000	Marketing	1
11/01/2018	Ben	32000	Sales	0
11/01/2018	Christine	34000	Finance	0
12/01/2018	Adam	30000	Marketing	1
12/01/2018	Ben	32000	Sales	0
12/01/2018	Christine	34000	Finance	0
13/01/2018	Adam	30000	Marketing	1
13/01/2018	Ben	32000	Sales	0
13/01/2018	Christine	34000	Finance	0
.
.
.
12/01/2023	Adam	30000	Marketing	0
12/01/2023	Ben	32000	Sales	1
12/01/2023	Christine	34000	Finance	1

[AlB · Accepted solution]

Hi @perryn27 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJzFXSUTI2AAIg7ZtYlJ1akpmXDmQbGuobGOobGRhagBXoG1gCOUYGSrE60UpOqXkgQSOIruDEnNRikA4LfQMzkA5LIAeszjmjKLMYaF4qSLUJRLVbZl5iXjJIxNBIH4iAhhqB1ccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Pay Rate" = _t, Division = _t, #"Hire Date" = _t, #"Termination Date" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Hire Date", type date}, {"Termination Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Name", type text}, {"Pay Rate", Int64.Type}, {"Division", type text}}),

    firstDate_ = List.Min(#"Changed Type"[Hire Date]),
    lastDate_ = Date.From(DateTime.LocalNow()),
    numMonths_ = Duration.Days(lastDate_ - firstDate_),
    daysList_ = List.Generate(()=>firstDate_, each _ <= lastDate_, each Date.AddDays(_, 1)),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each daysList_),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Employed?", each if [Date]>= [Hire Date] and ([Date]<=[Termination Date] or [Date]=null) then 1 else 0, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Hire Date", "Termination Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Name", "Pay Rate", "Division", "Employed?"})
in
    #"Reordered Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.