PIVOT TABLE SUM OF DISTINCT CHANGE ORDER #

[mad3]

I have a large data export with change orders. Each has one or more lines and the total amount for the project is listed on each line. I only want to sum the amount once for each CHANGE ID.

Change Id	Order #	Lineitem	Change Reason	Amount
CH55910	5302704	1	Miscalculation	$710.96
CH55910	5302704	2	Miscalculation	$710.96
CH56517	5292933	1	Miscalculation	$237.24
CH56521	5292933	1	Project Scope Changed	$332.64
CH56522	5292933	1	Miscalculation	$1,972.84
CH57039	5294333	1	Miscalculation	$386,009.71
CH57039	5294333	2	Miscalculation	$386,009.71
CH57039	5294333	3	Miscalculation	$386,009.71
CH57674	5303849	1	Scope Change	$19,583.67
CH57674	5303849	2	Scope Change	$19,583.67
CH57820	5300694	1	Scope Change	$21,721.00
CH58541	5306865	1	Project Scope Changed	$1,094.70
CH58541	5306865	2	Project Scope Changed	$1,094.70
CH58635	5288992	1	Scope Change	$367,507.41
CH58635	5288992	2	Scope Change	$367,507.41
CH58635	5288992	3	Scope Change	$367,507.41
CH58639	5288999	1	Scope Change	$99,100.00
CH58639	5288999	2	Scope Change	$99,100.00
CH58649	5288991	1	Scope Change	$279,300.00
CH58649	5288991	2	Scope Change	$279,300.00

 

[alansidman]

With Power Query

Book2
	A	B	C	D	E	F	G	H	I
1	Change Id	Order #	Lineitem	Change Reason	Amount		Change Id	Order #	Amount
2	CH55910	5302704	1	Miscalculation	$710.96		CH55910	5302704	710.96
3	CH55910	5302704	2	Miscalculation	$710.96		CH56517	5292933	237.24
4	CH56517	5292933	1	Miscalculation	$237.24		CH56521	5292933	332.64
5	CH56521	5292933	1	Project Scope Changed	$332.64		CH56522	5292933	1972.84
6	CH56522	5292933	1	Miscalculation	$1,972.84		CH57039	5294333	386009.71
7	CH57039	5294333	1	Miscalculation	$386,009.71		CH57674	5303849	19583.67
8	CH57039	5294333	2	Miscalculation	$386,009.71		CH57820	5300694	21721
9	CH57039	5294333	3	Miscalculation	$386,009.71		CH58541	5306865	1094.7
10	CH57674	5303849	1	Scope Change	$19,583.67		CH58635	5288992	367507.41
11	CH57674	5303849	2	Scope Change	$19,583.67		CH58639	5288999	99100
12	CH57820	5300694	1	Scope Change	$21,721.00		CH58649	5288991	279300
13	CH58541	5306865	1	Project Scope Changed	$1,094.70
14	CH58541	5306865	2	Project Scope Changed	$1,094.70
15	CH58635	5288992	1	Scope Change	$367,507.41
16	CH58635	5288992	2	Scope Change	$367,507.41
17	CH58635	5288992	3	Scope Change	$367,507.41
18	CH58639	5288999	1	Scope Change	$99,100.00
19	CH58639	5288999	2	Scope Change	$99,100.00
20	CH58649	5288991	1	Scope Change	$279,300.00
21	CH58649	5288991	2	Scope Change	$279,300.00
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source, {"Change Id"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"Change Id", "Order #", "Amount"})
in
    #"Removed Other Columns"