Forum Discussion
Solved
Custom Column Based on Groups in 2 Other Columns
Hello, I am trying to create a custom column that would designate which employees are still "in training." My data looks like below: Employee Name Training TrainingStatus DateTime Trai...
let status = [Complete = "Training Complete"], Source = your_data, group = Table.Group( Source, {"Employee Name", "Training"}, { {"rows", each _}, {"status", (x) => Record.FieldOrDefault(status, Table.Max(x, "TrainingStatus DateTime")[Training Status], "In Training")} } ), result = Table.ExpandTableColumn(group, "rows", {"TrainingStatus DateTime", "Training Status"}) in result
If I try to use your soltion but replace the Source with my dataflow, I run into the error "Token Identifier expected" on the second "let" statement. I masked the workspace and dataflow IDs in the code below.
let
Source = PowerPlatform.Dataflows(null),
Workspaces = Source{[Id="Workspaces"]}[Data],
#"workspace" = Workspaces{[workspaceId="workspace"]}[Data],
#"dataflow" = #"workspace"{[dataflowId="dataflow"]}[Data],
#"Current Employee Training Records_" = #"dataflow"{[entity="Current Employee Training Records",version=""]}[Data],
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, Training = _t, #"TrainingStatus DateTime" = _t, #"Training Status" = _t]),
#"Renamed Columns" = Table.RenameColumns(#"Current Employee Training Records_",{{"Old Training", "Training"}, {"Old Employee Name", "Employee Name"}, {"Old Status Namee", "Training Status"}, {"Old Training Date", "TrainingStatus DateTime"}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Employee Name", type text}, {"Training", type text},
{"TrainingStatus DateTime", type datetime},
{"Training Status", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name", "Training"}, {
{"Desired Result", (t)=>
[a=Table.Sort(t, each [TrainingStatus DateTime]),
b=Table.DuplicateColumn(a,"Training Status","Desired Result"),
c=Table.ReplaceValue(
b,
each [Training Status],
List.Last(b[Training Status]),
(x,y,z) as text => if z="Complete" then "Training Complete"
else if z="In Progress" then "In Training"
else z,
{"Desired Result"})][c],
type table[TrainingStatus DateTime=date, Training Status=text, Desired Result=text]
}}),
#"Expanded Desired Result" = Table.ExpandTableColumn(#"Grouped Rows", "Desired Result", {"TrainingStatus DateTime", "Training Status", "Desired Result"})
in
#"Expanded Desired Result"
Unless your data source code includes that second let, it looks like you only replaced part of the Source line from my code.
The Source line starts at Source = and ends at the comma before the #"Changed Type" step.
That second let is part of the Source statement.