cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Custom Column Help

Hey Guys, 

I've been tasked to find what the impact speed is for processing our tasks with and without automation. 

 

We collect data on both Manual and Automated tasks.  The Automated tasks are followed by the manual tasks that cant be automated. Each task is measured in time (seconds), but I need my Automated tasks to show the avg. time of my manual tasks so that I can get a total time assuming the automated tasks were done manually..... 

 

I'm thinking that creating a new column that replaces the current Automated time with the manual Avg time (for that task) and keeping the manual times the same will do the trick.  

 

Below I've provided a grid of what I think my table should look like -Green being the new column result - Notice in the new column, Manual tasks are the same as the actual column and the automated tasks are avg for the manual task specific to that task (task1, task2, task3).

zeke101_0-1601072294903.png

 

Appreciate the help. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

@zeke101 

you can try to create a column like this

Column = 
VAR task="Mannual "&right('Table (2)'[Tasks],len('Table (2)'[Tasks])-find("Task",'Table (2)'[Tasks],1,0)+1)
RETURN if(FIND("Mannual",'Table (2)'[Tasks],1,0)<>0,'Table (2)'[Acutal Time Seconds],AVERAGEX(FILTER('Table (2)','Table (2)'[Tasks]=task),'Table (2)'[Acutal Time Seconds]))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Microsoft
Microsoft

Hi @zeke101 ,

 

You can use the following m query to split your task column to two colums(Task type and Task number):

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wciwtyc9NLElNUQhJLM42VNJRMlSK1YlW8k3MK03MgQsaG4BFUVUbYVMNEjTBptoYm2qwIDbVOF1iRJJLDI1JcompUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Actual Time Seconds" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Actual Time Seconds", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOfAny([Task],{"0".."9"})),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Task], [Custom]), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Task], [Custom], 1), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Range",{{"First Characters", "Task type"}, {"Text Range", "Tasknumber"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom"})
in
    #"Removed Columns"

 

Capture.PNG

 

Then use the following calculated column:

 

 

Column = IF('Table'[Task type] = "Automated Task",AVERAGEX(FILTER('Table','Table'[Tasknumber] =EARLIER('Table'[Tasknumber]) &&'Table'[Task type] = "Manual Task"),'Table'[Actual Time Seconds]),'Table'[Actual Time Seconds]) 

 

 

Capture1.PNG

 

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX1YRC4AdOdKpf59-f...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

@zeke101 

 

You can do this if you have a clear way to distinguish Automated vs Manual Task. 

 

Take this formula for example: 

Average Manual Time = IF(AutomationTasks[Automated]= "Manual", AutomationTasks[Actual Time], AVERAGEX(FILTER(ALL(AutomationTasks), AutomationTasks[Task]=EARLIER(AutomationTasks[Task]) && AutomationTasks[Automated]="Manual"), AutomationTasks[Actual Time]))
 
Works when your data looks more like the attached sample file (see below signature).
 
If you need help getting your data to look like that, please provide more accurate description of what your task names look like and how we can tell if they are automated or manual. 
 
 
 

 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Super User II
Super User II

@zeke101 

you can try to create a column like this

Column = 
VAR task="Mannual "&right('Table (2)'[Tasks],len('Table (2)'[Tasks])-find("Task",'Table (2)'[Tasks],1,0)+1)
RETURN if(FIND("Mannual",'Table (2)'[Tasks],1,0)<>0,'Table (2)'[Acutal Time Seconds],AVERAGEX(FILTER('Table (2)','Table (2)'[Tasks]=task),'Table (2)'[Acutal Time Seconds]))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Microsoft
Microsoft

Hi @zeke101 ,

 

You can use the following m query to split your task column to two colums(Task type and Task number):

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wciwtyc9NLElNUQhJLM42VNJRMlSK1YlW8k3MK03MgQsaG4BFUVUbYVMNEjTBptoYm2qwIDbVOF1iRJJLDI1JcompUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, #"Actual Time Seconds" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Actual Time Seconds", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOfAny([Task],{"0".."9"})),
    #"Inserted First Characters" = Table.AddColumn(#"Added Custom", "First Characters", each Text.Start([Task], [Custom]), type text),
    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Text Range", each Text.Middle([Task], [Custom], 1), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Range",{{"First Characters", "Task type"}, {"Text Range", "Tasknumber"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom"})
in
    #"Removed Columns"

 

Capture.PNG

 

Then use the following calculated column:

 

 

Column = IF('Table'[Task type] = "Automated Task",AVERAGEX(FILTER('Table','Table'[Tasknumber] =EARLIER('Table'[Tasknumber]) &&'Table'[Task type] = "Manual Task"),'Table'[Actual Time Seconds]),'Table'[Actual Time Seconds]) 

 

 

Capture1.PNG

 

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX1YRC4AdOdKpf59-f...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors