Forum Discussion
WHT7
1 year agoNew Member
Multiple conditions Index
Hey there peeps! I'm a bit of a novice when it comes to power query and M language, and I would appreciate some help with my current predicament. I currently have some data that shows agent ac...
- 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.
ToddChitt
1 year agoSuper User
Ah, got it, thanks.
And it's not sufficient just to know HOW MANY Breaks an Agent took? You really need them numbered in order?
- WHT71 year agoNew Member
For the sake of convenience yes that would be ideal. As each numbered break would indicate the duration that is allowed for agents. For example:
Break 1 means 15 minutes, break 2 means 30 minutes, break 3 means 15 minutes.