Results 1 to 10 of 14

Thread: Record related to other record

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016

    Record related to other record

    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

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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

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

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,679
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by horseyride View Post
    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.
    Last edited by Bob Phillips; 2019-10-23 at 09:19 PM.

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Ah. Load table1. Then for table2
    t1t2.xlsx

    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"}, {"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"

  5. #5
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016
    Yes, this is exactly what I'm looking for, thank you very very much.

  6. #6
    Seeker a.alfano's Avatar
    Join Date
    Oct 2019
    Posts
    5
    Articles
    0
    Excel Version
    2016
    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?

Posting Permissions

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