PowerPivot to compare two sets of summarized data

[mountainman88]

I have two sets up data which will always have identical column headings held in Table A and Table B. The data is run from the same system on two different dates to create the two data sets. The contents of the rows will change and although they do relate there is no unique identifier to link them. New rows can be added between A and B dates, rows can be removed, or existing Rows can have data changed. Below is very simplified example:

Table A					Table B
Initiative Num	PL	FY20			Initiative Num	PL	FY20
1	Revenue	1,000			1	Revenue	1,200
1	Revenue	5,000			1	COS	4,800
1	SGA	2,000			1	SGA	2,200
2	COS	8,000			2	COS	10,000
2	SGA	9,000			2	SGA	6,000
3	Revenue	4,000			3	Revenue	2,000
3	COS	6,000			3	COS	8,000
4	SGA	4,000			4	SGA	3,500
5	Revenue	1,200			5	Revenue	2,200
5	COS	5,800			5	COS	6,000
					6	COS	2,500
					6	COS	6,000
					7	SGA	7,000
		46,000					61,400

What I currently have to do is run SUMIFS() on both sets of data and then using the summarized data create the analysis below:

Analysis
Initiative Num	PL	A	B	Delta (A-B)
1	Revenue	6,000	1,200	4,800
1	COS	-	4,800	(4,800)
1	SGA	2,000	2,200	(200)
2	Revenue	-	-	-
2	COS	8,000	10,000	(2,000)
2	SGA	9,000	6,000	3,000
3	Revenue	4,000	2,000	2,000
3	COS	6,000	8,000	(2,000)
3	SGA	-	-	-
4	Revenue	-	-	-
4	COS	-	-	-
4	SGA	4,000	3,500	500
5	Revenue	1,200	2,200	(1,000)
5	COS	5,800	6,000	(200)
5	SGA	-	-	-
6	Revenue	-	-	-
6	COS	-	8,500	(8,500)
6	SGA	-	-	-
7	Revenue	-	-	-
7	COS	-	-	-
7	SGA	-	7,000	(7,000)
		46,000	61,400	(15,400)

Now in reality the data is far more complicated than this example and I have found that using a pivot table on the individual Tables is the only practical way to navigate them. Recreating the SUMIFs each time is a pain because I have to maintain multiple combinations of dimensions that may never have data in them. This is why a pivot table is ideal, but so far I can just pivot Table A or Table B, not a set of calcs between them.

I understand I can create a separate table with every combination of Initiative and PL column and bring in summarized data from both tables, then create excel calcs and pivot that data. But I want to avoid that level of maintenance.

I have been looking at PowerQuery and PowerPivot as a solution to this issue. My question is: is there away to setup connections to the two tables as data sources, summarise them and then create calculations between the two summarized Data tables per the Delta (A-B) calculation above, and then pivot THAT data? Maybe I need to maintain a separate relational table that lists every initiative number individually, and this can create the link between the two data sets somehow, and then use Dax expressions for the calcs?

 

[alansidman]

With Power Query, Bring each table into the Editor. Group the Rows on the Initiative and PL to get a sum of each category for each table. Then do a full join (merge) the two tables. After merging, here is the Mcode to get the expected results.

let
    Source = Table.NestedJoin(TableA, {"Initiative Num", "PL"}, TableB, {"Initiative Num", "PL"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Initiative Num", "PL", "Sum FY20"}, {"TableB.Initiative Num", "TableB.PL", "TableB.Sum FY20"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "A.FY20", each if[Sum FY20]= null then 0 else [Sum FY20]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "B.FY20", each if [TableB.Sum FY20]=null then 0 else [TableB.Sum FY20]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Sum FY20", "TableB.Sum FY20"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [A.FY20] - [B.FY20], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Difference"}})
in
    #"Renamed Columns"

Book4
	A	B	C	D	E	F	G
1	Initiative Num	PL	TableB.Initiative Num	TableB.PL	A.FY20	B.FY20	Difference
2	1	Revenue	1	Revenue	6000	1200	4800
3	1	SGA	1	SGA	2000	2200	-200
4			1	COS	0	4800	-4800
5	2	COS	2	COS	8000	10000	-2000
6	2	SGA	2	SGA	9000	6000	3000
7	3	Revenue	3	Revenue	4000	2000	2000
8	3	COS	3	COS	6000	8000	-2000
9	4	SGA	4	SGA	4000	3500	500
10	5	Revenue	5	Revenue	1200	2200	-1000
11	5	COS	5	COS	5800	6000	-200
12			6	COS	0	8500	-8500
13			7	SGA	0	7000	-7000
Sheet2

 

[mountainman88]

So this is really great but the real data im using is far more detailed and although I'd like to learn PowerPivot detailed mcode is beyond me (although I can manage basic stuff like arithmetic and text manipulation). The merged table works with your code im sure but I see that you have to jump from columns. Is there a way to have the two merged tables as separate data sources and go from there?

I have managed to create two tabs for Table A and Table B in Power query and grouped the data by Initiative Num and PL. I created a separate table which is just the initiative numbers and managed to get the Table A and Table B totals (Revenue+COS+SGA) for FY20 to come through by linking a pivot of the 3 tables.

The issue I have is when I try and bring in the PL category totals separately they are wrong. How would I make it work just using tables and making relationships between them? I tried creating a unique ID column for each data set which is =[initiaitve Num]&[PL] but it wouldnt let me me link them?

Thanks

 

[alansidman]

I'm a visual type and am having a hard time following your explanation. Can you show visually what you have and what you want. Don't understand why you cannot join the unique ID that you have created. So long as they are the same in each table there should be no issue.

 

[mountainman88]

The UID method wouldn’t be full outer even if it did work right?

I will try and play with it this weekend.

 

[mountainman88]

With Power Query, Bring each table into the Editor. Group the Rows on the Initiative and PL to get a sum of each category for each table. Then do a full join (merge) the two tables. After merging, here is the Mcode to get the expected results.

let
    Source = Table.NestedJoin(TableA, {"Initiative Num", "PL"}, TableB, {"Initiative Num", "PL"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Initiative Num", "PL", "Sum FY20"}, {"TableB.Initiative Num", "TableB.PL", "TableB.Sum FY20"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "A.FY20", each if[Sum FY20]= null then 0 else [Sum FY20]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "B.FY20", each if [TableB.Sum FY20]=null then 0 else [TableB.Sum FY20]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Sum FY20", "TableB.Sum FY20"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [A.FY20] - [B.FY20], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Difference"}})
in
    #"Renamed Columns"

Book4
	A	B	C	D	E	F	G
1	Initiative Num	PL	TableB.Initiative Num	TableB.PL	A.FY20	B.FY20	Difference
2	1	Revenue	1	Revenue	6000	1200	4800
3	1	SGA	1	SGA	2000	2200	-200
4			1	COS	0	4800	-4800
5	2	COS	2	COS	8000	10000	-2000
6	2	SGA	2	SGA	9000	6000	3000
7	3	Revenue	3	Revenue	4000	2000	2000
8	3	COS	3	COS	6000	8000	-2000
9	4	SGA	4	SGA	4000	3500	500
10	5	Revenue	5	Revenue	1200	2200	-1000
11	5	COS	5	COS	5800	6000	-200
12			6	COS	0	8500	-8500
13			7	SGA	0	7000	-7000
Sheet2

The simple thing that would get me the result I'm looking for: is there a way using your example, to output column A and C as a single column with no nulls, and the same for columns B&D like below:

Initiative Num (A and B merge)	PL (A & B merge but favor B)	A.FY20	B.FY20
1	Revenue	6000	1200
1	SGA	2000	2200
1	COS	0	4800
2	COS	8000	10000
2	SGA	9000	6000
3	Revenue	4000	2000
3	COS	6000	8000
4	SGA	4000	3500
5	Revenue	1200	2200
5	COS	5800	6000
6	COS	0	8500
7	SGA	0	7000

Is there a way to do this that doesnt involve lots of Mcode? perhaps by setting up relationships with other tables I could create?

thanks

 

[mountainman88]

So this is where Ive got to, I have three tabs in the data model.

1) A single column list of every initiative number from Table A and B using append and remove duplicates
2) Table A grouped by initiative and PL
3) Table B grouped by initiative and PL

I feel I'm close here, somehow I need to introduce the PL split into this

 

[alansidman]

let
    Source = Table.NestedJoin(TableA, {"Initiative Num", "PL"}, TableB, {"Initiative Num", "PL"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Initiative Num", "PL", "FY20"}, {"TableB.Initiative Num", "TableB.PL", "TableB.FY20"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded TableB",null,0,Replacer.ReplaceValue,{"FY20"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [FY20] - [TableB.FY20], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Initiative Num", "PL"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"TableB.Initiative Num", "TableB.PL", "FY20", "TableB.FY20", "Subtraction"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"TableB.Initiative Num", "Initiative"}, {"TableB.PL", "PL"}, {"FY20", "A"}, {"TableB.FY20", "B"}, {"Subtraction", "Delta (A-B)"}})
in
    #"Renamed Columns"

Book1
	A	B	C	D	E
1	Initiative	PL	A	B	Delta (A-B)
2	1	Revenue	1000	1200	-200
3	1	Revenue	5000	1200	3800
4	1	COS	0	4800	-4800
5	1	SGA	2000	2200	-200
6	2	COS	8000	10000	-2000
7	2	SGA	9000	6000	3000
8	3	Revenue	4000	2000	2000
9	3	COS	6000	8000	-2000
10	4	SGA	4000	3500	500
11	5	Revenue	1200	2200	-1000
12	5	COS	5800	6000	-200
13	6	COS	0	2500	-2500
14	6	COS	0	6000	-6000
15	7	SGA	0	7000	-7000
Sheet2