Forum Discussion
Combining tables analytical accounting
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 | 50% | € 50.00 | |
420 | 252 | Digital | 50% | € 75.00 |
421 | 252 | 50% | € 75,00 | |
422 | 253 | 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% | 50% | 25% | € 25.00 | |
251 | Pears | 50% | Digital | 50% | 25% | € 25.00 |
251 | Pears | 50% | 50% | 25% | € 25.00 | |
252 | Apples | 100% | Digital | 50% | 50% | € 75.00 |
252 | Apples | 100% | 50% | 50% | € 75.00 | |
253 | Pears | 25% | 100% | 25% | € 100.00 | |
253 | Bananas | 75% | 100% | 75% | € 300.00 | |
… |
|
|
|
|
|
|
I have absolutely no idea what to do.
Thanks in advance!
Jonas
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.
4 Replies
- AlexisOlson
Super User
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.
Daniel_FdrvcHelper I
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.
Many thanks! It works.
