FIFO Join in Power Query

[michaelsh]

My customers have services that can be activated and disactivated.

One customer can have several active services at the same time.

Activation and disactivation are Orders.

The disactivation order disactivates the earliest order of "activation" type (that hasn't been disactivated already) - FIFO.

I need to connect a disactivation date to all my "activation" orders.

Please see the sample.

Please help.

[michaelsh]

Geat, thanks a lot, @Anonymous 

I'll need to look into these List functions more...

[Anonymous]

Hi @michaelsh 

Try to transform your table and use Merge function in Power Query Editor.

I use your sample to have a test.

Firstly, pivot Order Type column as below.

Duplicate Orginal Data table and name it as Orginal Data (2). We will get active value in Orginal Data table, and get deactive value in Orginal Data (2). So we need to remove Service Disactivated in Orginal Data and remove Service Activated in Orginal Data (2).

Then remove null values in Service Activated and  Service Disactivated columns.

Add an Index column in two tables.

Then merge two tables by index column.

Expand Orginal Table 2 and reomve index column. Result is as below.

You can download the pbix file from this link.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[michaelsh]

Thanks, @Anonymous 

The result is not exactly what I expect.

Please see the order 3333. It shouldn't disconnect anything, since it happens before any Activation occurs.

The order that should disconnect 4444 is 7777, not 3333

Thanks!

[Anonymous · Accepted solution]

Hi @michaelsh 

OK, I think I make a mistake that keep 3333 which make disactivated happens before any Activation occurs.

Please try this way. Firstly, we need to add a Flag in Original Table to find Order Number happens before any Activation occurs like 3333.

Add an Index in Original Table. Then build three custom columns by M query.

Count Activated Before:

List.Count(
    Table.SelectRows(
        #"Added Index",
     (r) =>r[Order Type] = "Service Activated"
     and [Index] >= r[Index] )[Order Number]
     )

Count Order Per Type:

List.Count(
    Table.SelectRows(
        #"Added Custom",
         (r) =>[Order Type] = r[Order Type]
         and [Index] >= r[Index] )[Order Number]
         )

Flag:

if [Order Type] = "Service Disactivated" and [Count Per Order Type]>[Count Activated Before] then "Delete" else "OK"

New Table is as below.

Filter Flag column, only show "OK" value and then remove Index and three custom columns. We will get a table without 3333. Then repeat the operation in my previous reply.

You can download the pbix file from this link.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.