Create custom index

[Barnee]

Hi everyone!

I have a problem and I can't figure how to solve it, and I hope you guys can help me.

So I have the following table which is in charge of listing the service orders about problems and incidenst.

Each service order has a different Order No. 

Furthermore there is a Prev. Order No. and Next Order No. column. These two are helping to show those problems which for some reason haven't been solved for the first time.

So for example there is this MJ00001 in the 1 st row which has a Next Order No (MJ00004) because the technician couldn't solve it for the first try and he went back later to the same problem and administrated it as MJ00004 in the 4 th row which has a Prev. Order No. (MJ00001). These two rows makes together the whole solution to the problem.

 

What I want is a calculated column (the orange Index column) in DAX (or M) which gives the same index for the connected service orders as the first part of the service order-chain. These service order-chains can have more then 3 or 4 parts. 

I hope everything is clear, but if not please ask and I would try to explain.

Thank you in advance!

Barna

[Barnee]

@Eric_Zhang, thank you! that works perfectly.

@MarcelBeug ,thank you as well, I will try your solution as soon as I can and will send my feedback!

Again, thank you guys for your help

Barna

[Eric_Zhang · Accepted solution]

---

@Barnee wrote:

Hi everyone!

I have a problem and I can't figure how to solve it, and I hope you guys can help me.

So I have the following table which is in charge of listing the service orders about problems and incidenst.

Each service order has a different Order No. 

Furthermore there is a Prev. Order No. and Next Order No. column. These two are helping to show those problems which for some reason haven't been solved for the first time.

So for example there is this MJ00001 in the 1 st row which has a Next Order No (MJ00004) because the technician couldn't solve it for the first try and he went back later to the same problem and administrated it as MJ00004 in the 4 th row which has a Prev. Order No. (MJ00001). These two rows makes together the whole solution to the problem.

 

What I want is a calculated column (the orange Index column) in DAX (or M) which gives the same index for the connected service orders as the first part of the service order-chain. These service order-chains can have more then 3 or 4 parts. 

I hope everything is clear, but if not please ask and I would try to explain.

Thank you in advance!

Barna

---

@Barnee

Another way in DAX.

rank = RANKX(yourTable,LEFT(PATH(yourTable[Order No],yourTable[Prev Order No]),7),,ASC,Dense)

You can see the breaking down columns "path" and "Root No" for easy understanding this DAX.

path = PATH(yourTable[Order No],yourTable[Prev Order No])

Root No = LEFT(PATH(yourTable[Order No],yourTable[Prev Order No]),7)

[MarcelBeug]

Below a quite advanced Power Query (M) Solution using List.Accumulate.

Note 1: if your table is very large, then I would recommend to split it in a table with Orders that have no Previous or Next Order number and a table that does have related orders.

Note 2: I included column RowID in the table and used it at the end of the query to sort the result back to the original sort order.

Add a column with nested lists, each containing the previous and next order number.

Add a column with grouped orders (which is the heart of the solution):

the first argument of List.Accumulate is the complete column that was previously added

the second argument is the start value, being a list with current order no and its previous and next order numbers (nulls removed)

Now List.Accumulate loops over the list with previous and next order numbers, if any of these numbers are already in the list (GroupSoFar), than both are added to the list (sorted, only distinct values and nulls removed).

Now you can extract the order numbers to a comma separated field, group on this column (with operation All Rows to get all data in nested tables  in column "AllData") and proceed with the rest of the code (which is quite straightforward).

let
    Source = ServiceOrders,
    AddedOrdersList = Table.AddColumn(Source, "OrdersList", each {[#"Prev. Order No."], [Next Order No]}),
    AddedOrdersGrouped = Table.AddColumn(AddedOrdersList, 
                         "OrdersGrouped", 
                         each List.Accumulate(AddedOrdersList[OrdersList],
                                              List.RemoveNulls({[Order No], [#"Prev. Order No."], [Next Order No]}), 
                                              (GroupSoFar,NewMembers) => 
                                                List.Sort(List.Distinct(GroupSoFar & 
                                                                        (if List.ContainsAny(GroupSoFar,NewMembers) 
                                                                            then List.RemoveNulls(NewMembers) 
                                                                            else {})
                                                                        )))),
    #"Extracted Values" = Table.TransformColumns(AddedOrdersGrouped, {"OrdersGrouped", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"OrdersList"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"OrdersGrouped"}, {{"AllData", each _, type table}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Added Index", "AllData", {"RowID", "Order No", "Prev. Order No.", "Next Order No", "Service Time (hours)"}, {"RowID", "Order No", "Prev. Order No.", "Next Order No", "Service Time (hours)"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded AllData",{"OrdersGrouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"RowID", "Order No", "Prev. Order No.", "Next Order No", "Index", "Service Time (hours)"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"RowID", Order.Ascending}})
in
    #"Sorted Rows"