Combining tables analytical accounting

[Jonas_]

Hello,

I have 3 different tables:

The first is from the classical accounting.

Booking reference	Amount
…
251	€ 100.00
252	€ 150.00
253	€ 400.00
…

The 2 other tables are from the analytical accounting. One for the cost centers, the other for the cost places.

	Booking reference	Cost center	Percentage	Amount
	…
305	251	Apples	50%	€ 50.00
306	251	Pears	50%	€ 50.00
307	252	Apples	100%	€ 150.00
308	253	Pears	25%	€ 100,00
309	253	Bananas	75%	€ 300,00
…

	Booking reference	Cost place	Percentage	Amount
	…
418	251	Digital	50%	€ 50.00
419	251	Print	50%	€ 50.00
420	252	Digital	50%	€ 75.00
421	252	Print	50%	€ 75,00
422	253	Print	100%	€ 400,00
…

I want to combine these 3 tables as follows.

Booking reference	Cost Center	Cost Center %	Cost Place	Cost Place %	Percentage	Ammount
…
251	Apples	50%	Digital	50%	25%	€ 25.00
251	Apples	50%	Print	50%	25%	€ 25.00
251	Pears	50%	Digital	50%	25%	€ 25.00
251	Pears	50%	Print	50%	25%	€ 25.00
252	Apples	100%	Digital	50%	50%	€ 75.00
252	Apples	100%	Print	50%	50%	€ 75.00
253	Pears	25%	Print	100%	25%	€ 100.00
253	Bananas	75%	Print	100%	75%	€ 300.00
…

I have absolutely no idea what to do.

Thanks in advance!

Jonas

[Jonas_]

Many thanks! It works.

[Daniel_Fdrvc]

Can you explain - last two columns in final table - how did they come here? Are these any calculations? Or maybe it doesn't matter?

%

Percentage

Ammount

[AlexisOlson]

The last column I gave is a calculated, yes. It's the initial Amount multiplied by the two percentage columns.

I neglected the final percentage column you initially asked for but it's the same logic, just the product of the other two percentage columns.

[AlexisOlson · Accepted solution]

Merge the 2nd and 3rd table into the first one, expanding the cost and percentage columns from each, and then define a new custom column that multiplies the percentages with the initial amount.

let
    Source = Centers,
    #"Grouped Rows" = Table.Group(Source, {"Booking reference"}, {{"Amount", each List.Sum([Amount]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Booking reference"}, Centers, {"Booking reference"}, "Centers", JoinKind.LeftOuter),
    #"Expanded Centers" = Table.ExpandTableColumn(#"Merged Queries", "Centers", {"Cost center", "Percentage"}, {"Cost center", "Center%"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Centers", {"Booking reference"}, Places, {"Booking reference"}, "Places", JoinKind.LeftOuter),
    #"Expanded Places" = Table.ExpandTableColumn(#"Merged Queries1", "Places", {"Cost place", "Percentage"}, {"Cost place", "Place%"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Places", "Custom", each [#"Center%"] * [#"Place%"] * [Amount], type number)
in
    #"Added Custom"

See the attached file.