Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: lookup initial entry in multiple transfers
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Nov 2016
    Posts
    109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: lookup initial entry in multiple transfers

    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

  2. #12
    Board Regular
    Join Date
    Nov 2017
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: lookup initial entry in multiple transfers

    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:


    Code:
    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

    Code:
    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"
    Last edited by horseyride; Oct 14th, 2019 at 09:30 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •