Forum Discussion
Multiple conditions Index
- 1 year ago
In your example, the breaks are numbered sequentially by Agent.
The following code will do that:
let Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Agent ID", Int64.Type}, {"Agent Name", type text}, {"Agent Email", type text}, {"Status", type text}, {"Status Duration", type number}}), //Add index column to be able to sort back to original order #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), //Group by Agent and Status // then add index column for each subgroup #"Grouped Rows" = Table.Group(#"Added Index", {"Agent ID", "Status"}, { {"ALL", (t)=>Table.AddIndexColumn(t,"idx",1,1,Int64.Type), type table [Agent ID=nullable number, Agent Name=nullable text, Agent Email=nullable text, Status=nullable text, Status Duration=nullable number, Index=number, idx=number]}}), #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Agent Name", "Agent Email", "Status Duration", "Index", "idx"}), #"Sorted Rows" = Table.Sort(#"Expanded ALL",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}), //null idx unless "Break" #"Null idx" = Table.ReplaceValue( #"Removed Columns", each [Status], null, (x,y,z)=>if y="Break" then x else z, {"idx"}), #"Merged Columns" = Table.CombineColumns( Table.TransformColumnTypes(#"Null idx", {{"idx", type text}}, "en-US"), {"Status", "idx"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Status"), #"Reorder Columns" = Table.ReorderColumns(#"Merged Columns", Table.ColumnNames(Source)) in #"Reorder Columns"I don't understand when you write breaks 1 and 3 =15 minutes and break 2=30 minutes. What if they log shorter or longer breaks? What if the first break is 30 minutes? etc.
If what you want does not depend merely on the entry order, please provide some examples where it does not.
Yup they are very similar, the only difference is that in the first screen shot, whenever any agents switched to break. It didn't add any incrementing number.
In the second screenshot, I'm hoping to find a way that allows me to add an incrementing number depending on how many times an agent switched to break throughout the day.
Or
In your example, the breaks are numbered sequentially by Agent.
The following code will do that:
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Agent ID", Int64.Type}, {"Agent Name", type text},
{"Agent Email", type text}, {"Status", type text},
{"Status Duration", type number}}),
//Add index column to be able to sort back to original order
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Group by Agent and Status
// then add index column for each subgroup
#"Grouped Rows" = Table.Group(#"Added Index", {"Agent ID", "Status"}, {
{"ALL", (t)=>Table.AddIndexColumn(t,"idx",1,1,Int64.Type),
type table [Agent ID=nullable number, Agent Name=nullable text,
Agent Email=nullable text, Status=nullable text,
Status Duration=nullable number, Index=number, idx=number]}}),
#"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL",
{"Agent Name", "Agent Email", "Status Duration", "Index", "idx"}),
#"Sorted Rows" = Table.Sort(#"Expanded ALL",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
//null idx unless "Break"
#"Null idx" = Table.ReplaceValue(
#"Removed Columns",
each [Status],
null,
(x,y,z)=>if y="Break" then x else z,
{"idx"}),
#"Merged Columns" = Table.CombineColumns(
Table.TransformColumnTypes(#"Null idx", {{"idx", type text}}, "en-US"),
{"Status", "idx"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Status"),
#"Reorder Columns" = Table.ReorderColumns(#"Merged Columns", Table.ColumnNames(Source))
in
#"Reorder Columns"
I don't understand when you write breaks 1 and 3 =15 minutes and break 2=30 minutes. What if they log shorter or longer breaks? What if the first break is 30 minutes? etc.
If what you want does not depend merely on the entry order, please provide some examples where it does not.
- WHT71 year agoNew Member
Hey!
Thanks alot it worked like magic, will need to dive in and to fully understand who it works properly.
To answer your question. Each agent has a schedule to follow, and within that schedule are breaks. depeding on which break they take they are given a certain duration and a limited number of breaks. Which is why I stated that it would be only for convenience's sake to have each break an agent takes be specified with a number. I hope that answers your question.