Forum Discussion
Custom Column Based on Groups in 2 Other Columns
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
Thank you, this worked for me too. Is there a way to see the performance of a table in Power Query to determine if a methd is more efficient?
In the power query editor on power bi there is a diagnostic tools tab which lets you do that. When I checked the small amount of data in your sample executed in a quarter of the time as the accepted. But it's not always reliable. You should compare on your large data set.
Can the source data be changed to a dataflow instead of the JSON file? I keep getting an error that the column Employee Name of the table wasn't found, or I get a syntax error in Advanced Editor when I try to replace the Source as my dataflow.
The JSON document is merely the representation of who Power BI interprets the pasting of your data into the blank table. Used as is there should be no errors (there are none here).
- If Employee Name is not found, it is probably named something else. (PQ is case-sensitive, and there may be spaces or some non-printing characters)
- What is the first Step that shows the "not found" error?
- You should be able to easily replace Source with another Source. But without seeing what you actually did, I can't comment on your Syntax error.
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"
- If Employee Name is not found, it is probably named something else. (PQ is case-sensitive, and there may be spaces or some non-printing characters)