Page 2 of 2 FirstFirst 1 2
Results 11 to 14 of 14

Thread: Record related to other record

  1. #11
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.


    Register for a FREE account, and/
    or Log in to avoid these ads!

    t1t2.2.xlsx

    For recursive, try,
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Merged Queries" = Table.NestedJoin(Source,{"PRODUCT"},Table1,{"PRODUCT"},"Table1",JoinKind.LeftOuter),
        #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"QUANTIY"}, {"QUANTITY"}),
        Prod_List=List.Buffer(#"Expanded Table1"[PRODUCT]),
        ProdRelated_List=List.Buffer(#"Expanded Table1"[PRODUCT RELATED]),
        Rules_List=List.Buffer(#"Expanded Table1"[RULES]),
        Quantity_List=List.Buffer(#"Expanded Table1"[QUANTITY]),
    
    Highest =  (t as text, r as number) as number=>
       let  Spot = List.PositionOf( ProdRelated_List, t ),
            a1=Quantity_List{Spot},
            a2=Rules_List{Spot}
            in if a1 <> null then a1+a2+r else @Highest(Prod_List{Spot},a2+r),
    
    Expected= Table.AddColumn( #"Expanded Table1", "xxxx", each if [QUANTITY] <> null then [QUANTITY]+[RULES] else  Highest( [PRODUCT RELATED],0), type number),
        #"Removed Other Columns" = Table.SelectColumns(Expected,{"PRODUCT RELATED", "xxxx"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"xxxx", "QUANTITY"}})
    
    in #"Renamed Columns"

  2. #12
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016
    Yes Mike, I think you really hit the point.
    I took a quick look at the link you provide and the files attached and I think there, there is the solution I'm looking for.
    I'm going to spend my weekend studing it.
    Thank you really really much, you gave me a really good advice.

  3. #13
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Mike, does the list accumulate method work any faster than a recursive function as above? Curious which way should go in future

  4. #14
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    60
    Articles
    0
    Excel Version
    Office 365
    Hi horseyride,

    The short answer is that List.Accumulate and List.Generate is supposed to be better. However, I can't say that from experience.

    Everything I read suggests that PQ recursion is slow and consumes more memory because it does not implement "tail end recursion". So I have learned to implement my looping with List.Accumulate and List.Generate. I have never done any comparisons to show one is better then the other in practice. Perhaps others can comment based on their experience.

    Regards,
    Mike

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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