Forum Discussion

WHT7's avatar
WHT7
New Member
1 year ago
Solved

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...
  • ronrsnfld's avatar
    ronrsnfld
    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.