Results 1 to 4 of 4

Thread: Aggregating qty in a relate tables with conditions

  1. #1
    Seeker jjsararas's Avatar
    Join Date
    Sep 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016, 365

    Question Aggregating qty in a relate tables with conditions



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

    Esteemed guru(s),

    I have two tables:Inventory and Sales. What I'm trying to produce is a complete list ofStockcodes from Inventory, and then aggregate the Sales.UnitQty and list itbeside each stockcode, even if there are no sales for the stockcode. I thenneed to filter Sales.Date for last 3 months, and I need to filterSales.SalesType = 'S'.

    I've created a queryon Inventory, and included the related Sales table as well. If I run:

    Table.AggregateTableColumn(#"RemovedOther Columns", "Sales", {{"UnitQty", List.Sum,"Sum of Sales.UnitQty"}})

    I get a listresembling what I need, except I'm stuck on how to add my two filter criteria.I'm also not sure if it will preserve the full list of stockcodes -includingthe nulls- once I add those criteria. If not, is there another step to forcethe correct type of join?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,682
    Articles
    0
    Excel Version
    O365
    Can you post sample data for us to work with?

  3. #3
    Seeker jjsararas's Avatar
    Join Date
    Sep 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016, 365
    Thanks Bill- as it usually happens, the moment I ask for help I figure out what's going on. I suspected and then confirmed that, when selecting tables and related tables throughout the UI, the join is an inner join by default. I simply needed to start by merging the Inventory with Sales using an outer join, and I'm now seeing all the records including nulls in my table. But I still haven't been able to figure out if there is a way to force the join type in the M code?

    Let
    Source = Odbc.DataSource("dsn=SysproCompanyH", [HierarchicalNavigation=true]),
    SysproCompanyH_Database = Source{[Name="SysproCompanyH",Kind="Database"]}[Data],
    dbo_Schema = SysproCompanyH_Database{[Name="dbo",Kind="Schema"]}[Data],
    InvMaster_Table = dbo_Schema{[Name="InvMaster",Kind="Table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(InvMaster_Table,{"StockCode", "InvMovements"}),
    Custom1 = #"Removed Other Columns",
    #"Expanded InvMovements" = Table.ExpandTableColumn(Custom1, "InvMovements", {"EntryDate", "MovementType", "TrnQty"}, {"InvMovements.EntryDate", "InvMovements.MovementType", "InvMovements.TrnQty"})

  4. #4
    Seeker jjsararas's Avatar
    Join Date
    Sep 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016, 365
    Thanks Bob, I figured out that by using the Navigator to join the tables together, they are inner joins by default. I just had to make my initial Table.NestedJoin a left outer, and then just do the sorting and grouping within the merge. Cheers.

Tags for this Thread

Posting Permissions

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