Forum Discussion
Unpivoting data based on dates
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 n...
Hi Anonymous
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.
AlB
Community Champion
Community ChampionHi Anonymous
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. |