Create sum of Expanded Table Column (number) after NestedJoin

[padinator]

Hello there!

I have the following problem. I am Joining two tables in the classical way by "OrderNo".

As i am joining these tables (LeftOuterJoin) i get several matching records in the second table so i usually have a 1:n relation between the joined tables.

Now, what I want to achieve is that as I expand just ONE single column of table 2 (which is an AMOUNT Column) and (as i mentioned) there are more than one matches - i do no want to get "n" identical rows but instead i want to Sum up this record into the Column i am currently expandig.

I hope you get my point but for further illustration lets say i have these two tables

Table 1

OrderNo	Item	ID
1234	ABC	XXXX

Table2

OrderNo	SalesAmount	CostAmount
1234	100	50
1234	50	20

As i join Table1 with Table2 based on OrderNo and then expand lets say SalesAmount - i want to still have a single record containing the summed up amount of 150 instead of two identical records with each 100 and 50 within the expanded SalesAmount col.

Any hint would be highly appreciated!

Greets!

[Thingsclump · Accepted solution]

Hi @padinator 

Below screenshot shows a solution. You actually don't have to expand also after join.

Table 2 column is created after merge which is shown in preview at the bottom.

Sales amount total column is created using list.sum.

 

Thanks,

thingsclump

www.thingsclump.com 

[padinator]

I havr to try this out on monday because i do not merge my tables in another table but do a nested join instead where i have to ExpandTableColumn afterwards which is automatically generating x lines of Table 1 - based on the No. of Matches (n) Table 2 has with it

[padinator]

One Solution would be to simply group the Columns back and create a sum by

= Table.Group(#"Erweiterte NAV Wertposten1",{"Entry No_", "Spedition", "Promised Delivery Date", "Customer No_", "Country", "ZIP Code", "Delivery Address", "Item Weight", "STP Spedition", "Item No_", "Quantity", "Cost", "Cost Type", "Order No_", "Pallets", "Packages", "Package Weight", "Lagerkosten", "Inventory Period", "KT Code", "KT Dim Code", "KT Name", "Marke Code", "Marke Dim Code", "Marke Name", "KTID", "MarkeID"},{"total", each List.Sum([Umsatz])})

Anyway, i am wondering if there is a more efficient way to do this since this takes a whole lot of time to calculate and i guess it is not very efficient.