Aggregating qty in a relate tables with conditions

[jjsararas]

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?

 

[Bob Phillips]

Can you post sample data for us to work with?

 

[jjsararas]

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"})

 

[jjsararas]

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.