Power Query: Averaging values when greater than zero

[RICH937]

Hi folks.
About 3 weeks ago, I began to try and learn how to use power query, and while I think I'm slowly getting the basics, I cannot for the life of me figure out how to do what would be an insanely easy task in Excel. I have a fairly large database that I'm working with, and am trying to calculate paretos (e.g. 80/20) using average monthly volume for about a dozen or so criteria. My goal is to end with a model that is responsive to monthly changes without the need for refreshing the multiple power pivot workbooks.

I've tried all the usual sources, and I just can't seem to get it right. I've tried unpivoting and grouping, but cannot figure out how to specify the "X>0" requirement for averaging. Tried an insanely long nested if/then, but that didn't work either. Most of what I am finding either (a) has only 1 column that is being averaged, or (b) is "averagingif" using other criteria than values. The latter define those variables using =>. Maybe that is the right way to go, but I can't figure it out.

I'm not sure if the BB will work for this, but if it does, the queries are in the mini sheet below.

One other Q. I have only seen one YT class show adding calculations inot a table loaded from PQ editor, and then reloading the results into editor using the "from table/range" option to get data. I can make that work, but it's going to get huge quick, and it seems to require almost as much refreshing as power pivot. I'm just not sold that is the best way forward.

Any help will be greatly appreciated.

CUST ID

 

[RICH937]

ran out of time trying to get the BB to load. hopefully this works.

POWER QUERY HELPER.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	ABRIDGED DATA SET. Actual Line Count: 21,522
2	CUST ID	CUST PROD ID	CHANNEL	PRODUCT	CUSTOMER	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19
3	1	1	Channel 1	Product 1	Customer 1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	9,175	2,350	2,600	2,450	14,450	3,450	0	1,825	23,150	125	0
4	2	2	Channel 2	Product 2	Customer 2	3,575	3,025	3,375	3,300	4,550	2,975	4,050	4,100	3,300	2,675	0	375	650	3,850	3,550	4,325	4,875	4,075	5,125	4,325	4,100	5,975	4,450	4,650	4,900	4,350	4,350	5,050	2,800	2,850	3,600	3,175	3,225	2,825	3,850	2,650
5	3	3	Channel 2	Product 3	Customer 3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3,500	0	0	0	2,800	0	0	2,800	0	0	0	0	0	0	0	0	0	0	0
6	4	4	Channel 3	Product 4	Customer 4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	25	0	4,500	0	0	0	0	4,500	0
7	5	5	Channel 2	Product 5	Customer 5	0	0	0	0	0	25	800	800	500	750	500	500	1,000	1,000	625	1,000	500	1,000	1,000	500	1,000	500	1,000	1,000	1,000	1,000	500	1,000	1,000	1,000	0	0	0	0	2,000	0
8	6	6	Channel 1	Product 6	Customer 6	0	0	0	0	0	0	2,000	1,000	2,200	0	3,000	1,025	1,000	1,500	0	0	650	300	0	350	1,000	4,150	175	3,000	3,075	0	0	0	0	0	0	0	0	0	0	0
9	7	7	Channel 4	Product 7	Customer 7	0	0	0	0	0	0	1,750	0	0	0	0	0	0	0	0	0	0	0	0	2,000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	8	8	Channel 5	Product 8	Customer 8	2,610	1,400	2,410	2,300	2,110	2,560	1,500	2,400	1,630	600	700	2,500	1,000	1,710	1,190	1,570	1,420	2,970	2,340	3,160	2,400	1,700	700	650	500	500	500	700	6,240	0	2,500	0	0	2,500	0	0
11	9	9	Channel 6	Product 9	Customer 9	350	390	480	450	600	560	660	720	680	1,180	1,070	1,320	1,080	1,350	1,150	1,850	1,550	1,180	2,020	1,710	1,100	2,450	2,200	2,950	3,050	2,850	2,500	2,270	2,850	2,270	2,650	2,670	3,300	3,200	2,950	3,700
12	5	10	Channel 2	Product 10	Customer 5	0	0	0	0	0	25	550	250	500	250	400	325	400	700	400	500	250	500	400	400	500	0	0	500	900	750	0	0	0	500	0	0	0	1,600	0	0
13	10	11	Channel 7	Product 11	Customer 10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	100	100	0	360	1,510	1,400	1,350	1,950	1,650	1,350	1,800	1,800	1,750	1,750	1,950	1,600	2,210	1,720	1,720	2,100	1,060	0
14	3	12	Channel 2	Product 12	Customer 3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,875	800	0	0	0	0	0	0	1,900	0	0	0	0	0	0	0	0	0	0	0
15	11	13	Channel 5	Product 13	Customer 11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	10	150	190	260	220	200	2,520	920	640	3,380	1,800	1,650	1,400	2,200	2,600	1,700	2,100	1,100
16	12	14	Channel 8	Product 14	Customer 12	1,175	1,250	1,275	900	1,200	975	975	1,300	1,600	1,525	1,150	1,450	2,275	925	1,300	1,075	1,150	800	1,125	1,025	925	1,850	1,250	1,025	1,150	1,300	1,250	1,750	675	1,275	1,575	575	1,525	1,725	1,900	1,150
17	9	15	Channel 6	Product 15	Customer 9	220	250	290	250	310	330	390	330	360	630	470	530	460	820	550	800	930	680	1,170	980	750	1,600	1,780	2,450	2,050	2,850	1,500	2,070	2,350	1,700	2,020	2,550	2,650	2,600	2,410	3,320
18	13	16	Channel 6	Product 16	Customer 13	0	0	0	1,250	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
19	14	17	Channel 6	Product 17	Customer 14	925	1,100	1,075	1,050	975	1,500	1,050	800	800	900	725	2,425	500	425	1,175	875	850	550	425	400	200	0	0	0	0	100	0	0	0	0	0	0	0	0	0	0
20	15	18	Channel 8	Product 18	Customer 15	925	825	650	650	1,025	975	800	1,150	750	925	1,000	850	625	1,125	1,100	850	1,150	1,200	1,025	1,000	1,500	1,200	900	1,450	900	1,425	675	1,275	1,250	1,100	775	1,300	1,250	1,300	1,500	825
21	16	19	Channel 1	Product 19	Customer 16	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,200	1,200	1,780	1,000	500
22	11	20	Channel 5	Product 20	Customer 11	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	10	150	130	310	400	0	860	1,080	640	2,460	1,400	1,650	1,400	1,900	1,960	1,500	1,700	1,000
23	17	21	Channel 6	Product 21	Customer 17	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	150	875	1,250	1,500	500	2,500	500	1,375
24	18	22	Channel 6	Product 22	Customer 18	950	900	875	875	800	400	1,075	1,225	825	1,050	675	925	900	850	1,100	600	1,200	1,250	1,025	900	750	875	1,125	1,200	500	1,300	1,500	1,075	1,475	1,050	1,500	1,050	1,300	1,250	1,000	800
25	19	23	Channel 9	Product 23	Customer 19	300	1,350	375	1,350	300	1,250	1,550	150	1,525	50	1,600	100	1,550	100	1,400	1,500	400	1,000	450	1,100	1,550	550	1,100	550	1,450	1,000	1,450	0	1,450	1,700	1,750	0	1,750	0	1,750	0
26	20	24	Channel 7	Product 24	Customer 20	1,325	700	1,350	1,325	1,325	900	1,325	1,325	650	1,325	1,300	1,300	725	1,300	1,350	1,300	1,350	2,000	725	1,375	1,300	1,325	675	725	675	700	725	750	800	800	675	775	750	700	75	1,325
27	21	25	Channel 10	Product 25	Customer 21	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	1,000	1,000	0	1,000	0	1,000	1,000	0	0
28	22	26	Channel 11	Product 26	Customer 22	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	0	0	0	0	0	0	0
29	7	27	Channel 4	Product 27	Customer 7	0	0	0	0	0	0	750	0	0	0	0	0	0	0	0	0	0	0	0	1,250	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30	7	28	Channel 4	Product 28	Customer 7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
31	23	29	Channel 12	Product 29	Customer 23	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	0	0	0	0	0	0	0	0	0	0	0	0
32	24	30	Channel 8	Product 30	Customer 24	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	0	0	0	0	0
33	24	31	Channel 8	Product 31	Customer 24	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	0	0	0	0	0
34	24	32	Channel 8	Product 32	Customer 24	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000	0	0	0	0	0	0	0
35	25	33	Channel 8	Product 33	Customer 25	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1,000
HELPER

POWER QUERY HELPER.xlsx
	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
2	CUST ID	CUST+PROD ID	CHANNEL	PRODUCT	CUSTOMER	TOTAL VOL	AVG VOL (need)	AVG VOL (wrong)		CUST ID	CUST PROD ID	CHANNEL	PRODUCT	CUSTOMER	TOTAL VOL	AVG VOL		CUST ID	CUST PROD ID	CHANNEL	PRODUCT	CUSTOMER	TOTAL VOL	AVG VOL
3	1	1	Channel 1	Product 1	Customer 1	59,575	6,619	1,655		1	1	Channel 1	Product 1	Customer 1	59,575	6,619		1	1	Channel 1	Product 1	Customer 1	59,575	6,619
4	2	2	Channel 2	Product 2	Customer 2	128,875	3,682	3,580		2	2	Channel 2	Product 2	Customer 2	128,875	3,682		2	2	Channel 2	Product 2	Customer 2	128,875	3,682
5	3	3	Channel 2	Product 3	Customer 3	9,100	3,033	253		3	3	Channel 2	Product 3	Customer 3	9,100	3,033		3	3	Channel 2	Product 3	Customer 3	9,100	3,033
6	4	4	Channel 3	Product 4	Customer 4	9,025	3,008	251		4	4	Channel 3	Product 4	Customer 4	9,025	3,008		4	4	Channel 3	Product 4	Customer 4	9,025	3,008
7	5	5	Channel 2	Product 5	Customer 5	21,500	827	597		5	5	Channel 2	Product 5	Customer 5	21,500	827		5	5	Channel 2	Product 5	Customer 5	21,500	827
8	6	6	Channel 1	Product 6	Customer 6	24,425	1,628	678		6	6	Channel 1	Product 6	Customer 6	24,425	1,628		6	6	Channel 1	Product 6	Customer 6	24,425	1,628
9	7	7	Channel 4	Product 7	Customer 7	3,750	1,875	104		7	7	Channel 4	Product 7	Customer 7	3,750	1,875		7	7	Channel 4	Product 7	Customer 7	3,750	1,875
10	8	8	Channel 5	Product 8	Customer 8	56,970	1,838	1,583		8	8	Channel 5	Product 8	Customer 8	56,970	1,838		8	8	Channel 5	Product 8	Customer 8	56,970	1,838
11	9	9	Channel 6	Product 9	Customer 9	63,310	1,759	1,759		9	9	Channel 6	Product 9	Customer 9	63,310	1,759		9	9	Channel 6	Product 9	Customer 9	63,310	1,759
12	5	10	Channel 2	Product 10	Customer 5	10,600	505	294		5	10	Channel 2	Product 10	Customer 5	10,600	505		5	10	Channel 2	Product 10	Customer 5	10,600	505
13	10	11	Channel 7	Product 11	Customer 10	29,230	1,462	812		10	11	Channel 7	Product 11	Customer 10	29,230	1,462		10	11	Channel 7	Product 11	Customer 10	29,230	1,462
14	3	12	Channel 2	Product 12	Customer 3	4,575	1,525	127		3	12	Channel 2	Product 12	Customer 3	4,575	1,525		3	12	Channel 2	Product 12	Customer 3	4,575	1,525
15	11	13	Channel 5	Product 13	Customer 11	23,040	1,280	640		11	13	Channel 5	Product 13	Customer 11	23,040	1,280		11	13	Channel 5	Product 13	Customer 11	23,040	1,280
16	12	14	Channel 8	Product 14	Customer 12	45,350	1,260	1,260		12	14	Channel 8	Product 14	Customer 12	45,350	1,260		12	14	Channel 8	Product 14	Customer 12	45,350	1,260
17	9	15	Channel 6	Product 15	Customer 9	45,400	1,261	1,261		9	15	Channel 6	Product 15	Customer 9	45,400	1,261		9	15	Channel 6	Product 15	Customer 9	45,400	1,261
18	13	16	Channel 6	Product 16	Customer 13	1,250	1,250	35		13	16	Channel 6	Product 16	Customer 13	1,250	1,250		13	16	Channel 6	Product 16	Customer 13	1,250	1,250
19	14	17	Channel 6	Product 17	Customer 14	18,825	856	523		14	17	Channel 6	Product 17	Customer 14	18,825	856		14	17	Channel 6	Product 17	Customer 14	18,825	856
20	15	18	Channel 8	Product 18	Customer 15	37,225	1,034	1,034		15	18	Channel 8	Product 18	Customer 15	37,225	1,034		15	18	Channel 8	Product 18	Customer 15	37,225	1,034
21	16	19	Channel 1	Product 19	Customer 16	5,680	1,136	158		16	19	Channel 1	Product 19	Customer 16	5,680	1,136		16	19	Channel 1	Product 19	Customer 16	5,680	1,136
22	11	20	Channel 5	Product 20	Customer 11	18,550	1,091	515		11	20	Channel 5	Product 20	Customer 11	18,550	1,091		11	20	Channel 5	Product 20	Customer 11	18,550	1,091
23	17	21	Channel 6	Product 21	Customer 17	8,650	1,081	240		17	21	Channel 6	Product 21	Customer 17	8,650	1,081		17	21	Channel 6	Product 21	Customer 17	8,650	1,081
24	18	22	Channel 6	Product 22	Customer 18	36,150	1,004	1,004		18	22	Channel 6	Product 22	Customer 18	36,150	1,004		18	22	Channel 6	Product 22	Customer 18	36,150	1,004
25	19	23	Channel 9	Product 23	Customer 19	33,450	1,045	929		19	23	Channel 9	Product 23	Customer 19	33,450	1,045		19	23	Channel 9	Product 23	Customer 19	33,450	1,045
26	20	24	Channel 7	Product 24	Customer 20	37,050	1,029	1,029		20	24	Channel 7	Product 24	Customer 20	37,050	1,029		20	24	Channel 7	Product 24	Customer 20	37,050	1,029
27	21	25	Channel 10	Product 25	Customer 21	6,000	1,000	167		21	25	Channel 10	Product 25	Customer 21	6,000	1,000		21	25	Channel 10	Product 25	Customer 21	6,000	1,000
28	22	26	Channel 11	Product 26	Customer 22	1,000	1,000	28		22	26	Channel 11	Product 26	Customer 22	1,000	1,000		22	26	Channel 11	Product 26	Customer 22	1,000	1,000
29	7	27	Channel 4	Product 27	Customer 7	2,000	1,000	56		7	27	Channel 4	Product 27	Customer 7	2,000	1,000		7	27	Channel 4	Product 27	Customer 7	2,000	1,000
30	7	28	Channel 4	Product 28	Customer 7	1,000	1,000	28		7	28	Channel 4	Product 28	Customer 7	1,000	1,000		7	28	Channel 4	Product 28	Customer 7	1,000	1,000
31	23	29	Channel 12	Product 29	Customer 23	1,000	1,000	28		23	29	Channel 12	Product 29	Customer 23	1,000	1,000		23	29	Channel 12	Product 29	Customer 23	1,000	1,000
32	24	30	Channel 8	Product 30	Customer 24	1,000	1,000	28		24	30	Channel 8	Product 30	Customer 24	1,000	1,000		24	30	Channel 8	Product 30	Customer 24	1,000	1,000
33	24	31	Channel 8	Product 31	Customer 24	1,000	1,000	28		24	31	Channel 8	Product 31	Customer 24	1,000	1,000		24	31	Channel 8	Product 31	Customer 24	1,000	1,000
34	24	32	Channel 8	Product 32	Customer 24	1,000	1,000	28		24	32	Channel 8	Product 32	Customer 24	1,000	1,000		24	32	Channel 8	Product 32	Customer 24	1,000	1,000
35	25	33	Channel 8	Product 33	Customer 25	1,000	1,000	28		25	33	Channel 8	Product 33	Customer 25	1,000	1,000		25	33	Channel 8	Product 33	Customer 25	1,000	1,000
HELPER

Cell Formulas
Range	Formula
AQ3:AQ35	AQ3	=Cal_For_Help[@[CUST ID]]
AR3:AR35	AR3	=Cal_For_Help[@[CUST PROD ID]]
AS3:AS35	AS3	=Cal_For_Help[@CHANNEL]
AT3:AT35	AT3	=Cal_For_Help[@PRODUCT]
AU3:AU35	AU3	=Cal_For_Help[@CUSTOMER]
AV3:AV35	AV3	=SUM(Cal_For_Help[@[Jan-17]:[Dec-19]])
AW3:AW35	AW3	=ROUND(AVERAGEIF(Cal_For_Help[@[Jan-17]:[Dec-19]],">0"),0)
AX3:AX35	AX3	=ROUND(AVERAGE(Cal_For_Help[@[Jan-17]:[Dec-19]]),0)
BF3:BF35	BF3	=ROUND([@[TOTAL VOL]]/SUM(IF(FILTER(Cal_For_Help[[Jan-17]:[Dec-19]],Cal_For_Help[CUST PROD ID]=[@[CUST PROD ID]])>0,1,0)),0)

 

[shift-del]

Hello RICH937

It is not clear to me what you want. You already have a average calcuation.
Please show a mini sheet with the expected outcome.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.AddColumn(Source, "Average Volume", each Number.Round(List.Average(List.RemoveItems(List.Skip(Record.ToList(_),5), {0})))),
    Result = Table.RemoveColumns(tbl, List.Skip(Table.ColumnNames(Source),5))
in
    Result

 

[RICH937]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.AddColumn(Source, "Average Volume", each Number.Round(List.Average(List.RemoveItems(List.Skip(Record.ToList(_),5), {0})))),
    Result = Table.RemoveColumns(tbl, List.Skip(Table.ColumnNames(Source),5))
in
    Result

I think this is almost there. What I get though is just an Avg Vol col full of errors. I'm attaching screenshots of both the adv ed in case I entered it wrong, and the result step with error descriptor. Thanks Jeff.

 

[RICH937]

Hello RICH937

It is not clear to me what you want. You already have a average calcuation.
Please show a mini sheet with the expected outcome.

Sorry for the confusion Del. The average calculation is the outcome I am attempting to calculate in Power Query. The values that are there weren't calculated using M. It was done with =ROUND(AVERAGEIF(Cal_For_Help[@[Jan-17]:[Dec-19]],">0"),0)
I can post a minisheet that is blank for the areas I'm looking for if that would help?

 

[JGordon11]

Does the code I provided in post #4 work correctly with the sample table you provided in the original post? (because it does for me). If so, the sample table is likely not representative of the actual table in some respect.

 

[RICH937]

Does the code I provided in post #4 work correctly with the sample table you provided in the original post? (because it does for me). If so, the sample table is likely not representative of the actual table in some respect.

yes. This works perfectly. Thank you. Only thing I had to do was add a reference table to the original table as it promoted "avg vol" to column header, but stripped the "change type" and promoted headers steps. I can't tell you how much this helps.