Excel power Query - merging 2 tables, calculate values and create pivot

Question

I have a task to complete. Its about business unit (BU) and cost centre (CC) "profit and loss" - PNL.
I have 2 tables.
1st table is main table and cosists of yearly data of Business Unit (BU) and 2 Cost centres (CC). BU and both CCs have 2 account and each accoutn has 5 flows, lets name them from flow1 to flow5. Each flow has yearly PNL data.

BU/CC	Account	Flows	Total 2023
Business Unit 1	Account 1	1) Project Revenue	120000
Business Unit 1	Account 1	2) Project Cost	-80000
Business Unit 1	Account 1	3) Other Revenue	0
Business Unit 1	Account 1	4) Other Costs	0
Business Unit 1	Account 1	5) Cost of work	-210000
Business Unit 1	Account 2	1) Project Revenue	600000
Business Unit 1	Account 2	2) Project Cost	-110000
Business Unit 1	Account 2	3) Other Revenue	0
Business Unit 1	Account 2	4) Other Costs	0
Business Unit 1	Account 2	5) Cost of work	-245000
Cost center 1	Account 1	1) Project Revenue	0
Cost center 1	Account 1	2) Project Cost	0
Cost center 1	Account 1	3) Other Revenue	0
Cost center 1	Account 1	4) Other Costs	-120000
Cost center 1	Account 1	5) Cost of work	-210000
Cost center 1	Account 2	1) Project Revenue	0
Cost center 1	Account 2	2) Project Cost	0
Cost center 1	Account 2	3) Other Revenue	0
Cost center 1	Account 2	4) Other Costs	-120000
Cost center 1	Account 2	5) Cost of work	-210000
Cost center 2	Account 1	1) Project Revenue	0
Cost center 2	Account 1	2) Project Cost	0
Cost center 2	Account 1	3) Other Revenue	0
Cost center 2	Account 1	4) Other Costs	-120000
Cost center 2	Account 1	5) Cost of work	-465000
Cost center 2	Account 2	1) Project Revenue	0
Cost center 2	Account 2	2) Project Cost	0
Cost center 2	Account 2	3) Other Revenue	0
Cost center 2	Account 2	4) Other Costs	-120000
Cost center 2	Account 2	5) Cost of work	-210000

2nd table is allocation table, which presents what partion of CC's PNL is allocated to BU defined as indirect cost. table consists of 3 columns: BU/CC/allocation key.

BU	CC	Allocation Key
Business Unit 1	Cost center 1	70
Business Unit 1	Cost center 2	30

What I need to do?
Show pivot table of complete P&L where only BUs are shown as lines on the top level. The costs of CCs are distributed towards BUs (let’s call them indirect costs) via allocation key (e.g. BU1 will receive 70% of costs from CC1, 30% of costs from CC2 etc). Allocation key for cost distribution should be defined in allocation table –(which MUST BE ABLE to manually change the allocation "keys") (instructions refer to multiple BUs and CCs but I gave you example of only 1 BU an 2 CCs.

Pivot table construction:

I'm kind of stucked at this example at mergin these 2 tables and calculating accurate data in regard to INDIRECT costs and lost at pivoting table in power query in Excel.

I would really appreciate your ideas/suggestions/help!

Thanks!

Answer 1

In powerquery this code will duplicate and allocate the dollars Its up to you to make a report out of it

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"BU/CC"}, alloc, {"BU"}, "alloc", JoinKind.LeftOuter),
#"Expanded alloc" = Table.ExpandTableColumn(#"Merged Queries", "alloc", {"CC", "Allocation Key"}, {"CC", "Allocation Key"}),
#"Added Custom" = Table.AddColumn(#"Expanded alloc", "BU/CC2", each [CC] ?? [#"BU/CC"]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Total 20232", each if [CC]=null then [Total 2023] else [Total 2023]*[Allocation Key]/100),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([CC] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Total 20232"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns", "Total 20232", each [Total 2023]*[Allocation Key]/100),
Combined =  #"Added Custom1"  & #"Added Custom2",
#"Added Custom3" = Table.AddColumn(Combined, "Allocated", each if [CC]=null then "NotAllocated" else "Allocated"),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"BU/CC", "Total 2023", "CC", "Allocation Key"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"BU/CC2", "BU/CC"}, {"Total 20232", "Total 2023"}})
in  #"Renamed Columns"