# Thread: Record related to other record

1. Register for a FREE account, and/

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. 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. Mike, does the list accumulate method work any faster than a recursive function as above? Curious which way should go in future

4. 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 First 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
•