Multiple conditions Index

[WHT7]

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 activities. The data includes (agent name, agent ID, email, Status, and duration spent in status) The problem that I'm facing right now is whenever agents put the status break, it marks it as break, so there is no way to differentiate between break 1, 2 or 3 aside from the duration spent in the break. I wanted to make it easier to precieve by creating an index coulmn that would count depending on how many times a certain agent put break in the day, and then start over whenever the agent name changes. And when it comes to other status (offline, available, meeting, ect)it would just show as null, so that I can merge those two column in the end. 

If you have another method that could help me acheieve the same goal, I would be more than happy to take a look to learn more about the M language. 

[ToddChitt]

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?

[WHT7]

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.

[ToddChitt]

I don't see the difference in the two.

[WHT7]

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

[ronrsnfld · Accepted solution]

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.

[WHT7]

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.

[ToddChitt]

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.

[WHT7]

Sorry for confusing you :D. The data looks like this:

And I would like it to look like this:

I