lookup initial entry in multiple transfers

[dorinb]

Hi,

I have two columns with Entry no and TRansferred from entry no and would need to calculate a column Expected initial Entry no, that brings the initial entry no if an item has transferred ( it's differnt than zero).

It is easy to bring the value if we have one transfer for example entry 2 but I couldnt find a solution where you have multiple transfers. Eg. entry no 5, the initial entry no is 1.
[TABLE="width: 708"]
<tbody>[TR]
[TD]Entry no
[/TD]
[TD]Transferred from entry no
[/TD]
[TD]Expected result initial entry
[/TD]
[TD]Comment how expected should work
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]Entry no is 1 and it's transferred from 0 then bring Entry no 1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Entry no 2 is coming from entry no 1, and entry no 1 is transferred from zero than bring 1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]Entry no 3 is transferred from 0 then bring Entry no 3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Entry no 4 is transferred from 2, and 2 it's transferred from 1, and 1 is transferred from 0, then bring 1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]Entry no 5 is transferred from 4, and 4 it's transferred from 2, 2 is transferred from 1 and 1 is transferred from 0, then bring 1
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]Entry no 6 is transferred from 5, and 5 is transferred from 4, 4 is transferred from 2, 2 from 1 and 1 from 0 then 1
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]Entry 7 is transferred from 0 then bring Entry no 7
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]Entry no 8 is transferred from 4, and 4 it's transferred from 2, 2 is transferred from 1 and 1 is transferred from 0, then bring 1
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]Entry no 9 is transferred from 1, 1 is tranferred from zero then bring 1
[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be much appreciated.

 

[Special-K99]

1. Presumably there will be hundreds of Entry No.s ?
2. Are Entry No.s ever duplicated in that column?

 

[Special-K99]

This looked like the most interesting problem I've ever attempted on this board!
I was quite disappointed to solve it on my first attempt in such a short space of time.
I thought it was going to be much more complex

Not sure if this will work but runs on given data.
in C2
=IF(B2=0,A2,IF(VLOOKUP(B2,A$2:C$10,3,0)<>0,VLOOKUP(B2,A$2:C$10,3,0),"XXX"))

copy down the column

This assumes that Excel will resolve column C in row order, ie passing down the value previously calculated.
e.g.
8 looks at 4 but if 4 already has a result then just display that value, no need to look further back up the "chain".

NOTE: This solution may only work if column A is in numerical order.
UPDATE: I just swapped Entries 8 and 3 and associated values round and it worked, so you should be ok.

XXX should never be displayed and is only there for IF syntax reasons.
It means a previous value in the "chain" hasn't been calculated.

 

[dorinb]

This looked like the most interesting problem I've ever attempted on this board!
I was quite disappointed to solve it on my first attempt in such a short space of time.
I thought it was going to be much more complex

Not sure if this will work but runs on given data.
in C2
=IF(B2=0,A2,IF(VLOOKUP(B2,A$2:C$10,3,0)<>0,VLOOKUP(B2,A$2:C$10,3,0),"XXX"))

copy down the column

This assumes that Excel will resolve column C in row order, ie passing down the value previously calculated.
e.g.
8 looks at 4 but if 4 already has a result then just display that value, no need to look further back up the "chain".

NOTE: This solution may only work if column A is in numerical order.
UPDATE: I just swapped Entries 8 and 3 and associated values round and it worked, so you should be ok.

XXX should never be displayed and is only there for IF syntax reasons.
It means a previous value in the "chain" hasn't been calculated.

Thanks very much Special-K99, it works in excel, but I have over 1 mln rows and need a solution in power query.
If only there is any way to translate that function in power query.

 

[dorinb]

1. Unfortunately there are over 1 mln rows
2. Entry no is not duplicated and it's increasing incrementally if that helps.

 

[Special-K99]

Sorry, I didnt see it was a Power BI problem.
I don't know Power Query

 

[dorinb]

Sorry, I didnt see it was a Power BI problem.
I don't know Power Query

No worries thanks anyway. Your solution is still helpful.

 

[horseyride]

No idea how efficient this is for a million records, but this will traverse up the tree to find the relationship
Assumes data is in Table1 with columns headers [Entry no] and [Transferred from entry no]

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry no", type text}, {"Transferred from entry no", type text}}),
    Entry_List=List.Buffer(#"Changed Type"[Entry no]),
    Transfer_List=List.Buffer(#"Changed Type"[Transferred from entry no]),

Highest =  (n as text) as text=>
   let  Spot = List.PositionOf( Entry_List, n ),
        Transfer=Transfer_List{Spot}
        in if Transfer="0" then n else @Highest(Transfer),


Expected= Table.AddColumn( #"Changed Type", "Expected result initial entry", each  Highest( [Entry no]), type text)

in Expected

This substitution to above might speed it up a fraction

Expected = Table.AddColumn( #"Changed Type", "Expected result initial entry", each  if [Transferred from entry no] ="0" then [Entry no] else Highest( [Entry no]), type text)

 

[dorinb]

No idea how efficient this is for a million records, but this will traverse up the tree to find the relationship
Assumes data is in Table1 with columns headers [Entry no] and [Transferred from entry no]

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry no", type text}, {"Transferred from entry no", type text}}),
    Entry_List=List.Buffer(#"Changed Type"[Entry no]),
    Transfer_List=List.Buffer(#"Changed Type"[Transferred from entry no]),

Highest =  (n as text) as text=>
   let  Spot = List.PositionOf( Entry_List, n ),
        Transfer=Transfer_List{Spot}
        in if Transfer="0" then n else @Highest(Transfer),


Expected= Table.AddColumn( #"Changed Type", "Expected result initial entry", each  Highest( [Entry no]), type text)

in Expected

This substitution to above might speed it up a fraction

Expected = Table.AddColumn( #"Changed Type", "Expected result initial entry", each  if [Transferred from entry no] ="0" then [Entry no] else Highest( [Entry no]), type text)

Thanks horseyride,

Your formula is working, but it is quite slow. for 1 mln rows it took 3 hours.
The vlookup formula from Special-K99 is much much faster .
Any other ideas: a formula in power query to bring the first value of a lookup on a self referencing column similar with the formula in excel Special-K99 mentioned above?

Cheers,
Dorin

 

[horseyride]

You could also try (1) adding index (2) sorting on parent column (3) if parent equals row above then use row above else if column is zero then copy value over else call function. That would speed it up quite a bit if there are many parent duplicates. I will do code on Monday