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
Can you show us a sample of what the RAW data looks like (BEFORE you do anything with Power Query) and also what you WANT it to look like?
Personally, I don't really understand what your are trying to get to.
- WHT71 year agoNew Member
Sorry for confusing you :D. The data looks like this:
And I would like it to look like this:
I