Record related to other record

[a.alfano]

Hi everyone
I have to estimate the product sales through the sales of other product.
In other words I have a simple table like this:

PRODUCT QUANTIY A 10 B 20

I have another table that indicate the rules to estimate the quantity of other product.
In other words I have a simple table like this:

PRODUCT	PRODUCT RELATED	RULES
A	AA	+3
B	BB	+1
AA	AAA	+1
BB	BBB	+3

I'd like to produce a table like this

PRODUCT RELATED QUANTIY AA 13 BB 21 AAA 14 BBB 24

Thank you very much in advance

 

[horseyride]

I have no idea where the bottom two rows of your output table come from, but otherwise I'd suggest (a) in table 1, do a merge query with table2 use product as the matching key by clicking that column on top and bottom, join type left outer (2) expand merge by clicking arrows atop column (3) add column .. custom column ... that sums original Quantity and merged Rules Column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source,{"PRODUCT"},Table2,{"PRODUCT"},"Table2",JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"RULES"}, {"Table2.RULES"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Revised Quantity", each [QUANTIY]+[Table2.RULES])
in #"Added Custom"

 

[Bob Phillips]

I have no idea where the bottom two rows of your output table come from, ...

AAA = AAA from AA (+1), AA from A (+3), A is 10, =14.

BBB - similar.

That's the difficulty.

 

[horseyride]

Ah. Load table1. Then for table2
View attachment t1t2.xlsx

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"}, {"Table1.QUANTIY"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Sum", each [RULES]+[Table1.QUANTIY]),
    #"Merged Queries1" = Table.NestedJoin(#"Added Custom",{"PRODUCT"},#"Added Custom",{"PRODUCT RELATED"},"Added Custom",JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom", {"Sum"}, {"Sum.1"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Custom",null,0,Replacer.ReplaceValue,{"Table1.QUANTIY", "Sum", "Sum.1"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Quantity", each [RULES]+[Table1.QUANTIY]+[Sum.1]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"PRODUCT RELATED", "Quantity"})
in
    #"Removed Other Columns"

 

[a.alfano]

Yes, this is exactly what I'm looking for, thank you very very much.

 

[a.alfano]

Hi Neophyte,
unfortunatelly the files seem doest't work if you add other raw in the rules table.
If for example you add the line AAA / AAAA +10 in the table you will not get the 24 in the table result.
Any cues?

 

[AliGW]

You are the forum neophyte (newbie)! Which of the forum members helping you did you mean to address?

 

[a.alfano]

Reply to Horseyride

Yes, I m a neophyte and also my english is quite basic.
My intention was to reply to Horseyride but I wrote the wrong name.
The file attached from Hoseryide was good but unfortunatelly works only if I have 2 step in the table rule. Unfortunately I have a lot of step.
Sorry and excuse me for my mistake.

 

[horseyride]

Provide a more extensive example of the two tables and desired results, in table and not sentence format. Thanks

 

[cyborgski]

I think what a.alfano is trying to describe is a repeated lookup and consolidation of the table until the parts no longer have lookups. This problem is hard to describe in a forum setting even with good English. PowerQuery solutions would involve List.Generate or a Recursive function.

a.alfano, read the whole link below and see how similar it is to your process. If you can see how it applies then modify the code from the workbook in post #7. Hint, the recur function would look similar to horseyride's post #2 from this thread.
https://www.excelguru.ca/forums/sho...ctures-that-exist-in-only-2-columns-in-the-db

Regards,
Mike

 

[horseyride]

View attachment t1t2.2.xlsx

For recursive, try,

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"

 

[a.alfano]

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.

 

[horseyride]

Mike, does the list accumulate method work any faster than a recursive function as above? Curious which way should go in future

 

[cyborgski]

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