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.

 

[peter789]

Dorin,
Horseyride posted his code just before I was about to post something very similar using the same recursive function approach. I have run my version and his for a test file of 50000 records both from a table in the workbook and also from a tab delimited external text file. For both sets of code the local table refreshes in about 30 seconds and the external text file 40 seconds. Does your source data have additional columns that you do not show in your example data?

Peter

 

[horseyride]

Depending on how many times the rows are looking up the same value, this might work faster since it does not repeat the tree search if above row was looking up same value

If sorting matters:

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),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Transferred from entry no", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index2", 0, 1),

    Expected = Table.AddColumn( #"Added Index1", "Expected result initial entry", each if [Transferred from entry no] ="0" then [Entry no] else if #"Added Index1"{[Index2]}[Transferred from entry no] = #"Added Index1"{[Index2]-1}[Transferred from entry no] then null else  Highest( [Entry no]), type text),
    #"Filled Down" = Table.FillDown(Expected,{"Expected result initial entry"}),
    #"Sorted Rows1" = Table.Sort(#"Filled Down",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index", "Index2"})
in
    #"Removed Columns"

If sorting does not matter, this is probably faster still than the one above

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),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Transferred from entry no", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),

    Expected = Table.AddColumn( #"Added Index", "Expected result initial entry", each if [Transferred from entry no] ="0" then [Entry no] else if #"Added Index"{[Index]}[Transferred from entry no] = #"Added Index"{[Index]-1}[Transferred from entry no] then null else Highest( [Entry no]), type text),
    #"Filled Down" = Table.FillDown(Expected,{"Expected result initial entry"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"})
in
    #"Removed Columns"