Help! Is it possible to translate Excel Filter Function Formula (@cell) reference into PQE formula?

[RICH937]

Hi Everyone,
I am hoping this is an easy request for the PowerQuery users on here. I am trying to translate a simple simple excel filter function with multiple criteria like D2=FILTER(A2:A13, ((B2:B13=@B2) * (C2:C13="BLUE")))into a PQE formula. My goal is to be able to create basically the orange section below in PQE where values in columns are transposed to values in columns filtered for some criteria so I can do calculations on each entire row using the both the original and filtered data (last 4 columns). What I cannot figure out is how to write the cell reference like [@[PROD CLASS]] portion of the formula. FILTER([OH QTY],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BERMUDA"))).

I have looked for a solution here, on other boards, YT in M is for Data Monkey, Reddit, etc., and can't find anything. As it's a relatively common formula, and referencing a cell in a column like (@B2) is even more common, my bet is I am simply not using the correct search terms. I have tried pivot/group/unpivot, Table.SelectRows, and then Table.SelectRows along with an index and Table.ToList which seems like they should work, but nothing seems to work.

Can anyone help me translate this? If it's easier than doing the entire table below, just how to write: H3= FILTER([OH QTY],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BERMUDA"))) will most likely do the trick.

I've spent at least two days trying different things on this one, and nothing works so I'm throwing in the DIY towel.

Thank you,
Rich

P003 Baysian Source Paretos FINAL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	PROD_ID	PROD CLASS	SUBTYPE	CATEGORY	OH QTY	LIST/UNIT	BULK PROMO	BERM QTY	BLUE QTY	LEM QTY	ZOY QTY	BERM LSP	BLUE LSP	LEM LSP	ZOY LSP	BERM DIS	BLUE DIS	LEM DIS	ZOY DIS	TTL VOL	TTL VALUE	SUB % VOL	SUB % VALUE
3	G-1	GRASS-1	BERMUDA	SEED (BAG)	50	$250	20%	50	100	15	45	$250	$75	$225	$750	20%	12%	15%	18%	210	$57,125	23.8%	21.9%
4	G-1	GRASS-1	ZOYSIA	SEED (BAG)	45	$750	18%	50	100	15	45	$250	$75	$225	$750	20%	12%	15%	18%	210	$57,125	21.4%	59.1%
5	G-2	GRASS-2	BLUE	PLANT (FLAT)	5	$25	15%	10	5	20	10	$30	$25	$10	$50	10%	15%	7%	9%	45	$1,125	11.1%	11.1%
6	G-1	GRASS-1	LEMON	SEED (BAG)	15	$225	15%	50	100	15	45	$250	$75	$225	$750	20%	12%	15%	18%	210	$57,125	7.1%	5.9%
7	G-1	GRASS-1	BLUE	SEED (BAG)	100	$75	12%	50	100	15	45	$250	$75	$225	$750	20%	12%	15%	18%	210	$57,125	47.6%	13.1%
8	G-2	GRASS-2	BERMUDA	PLANT (FLAT)	10	$30	10%	10	5	20	10	$30	$25	$10	$50	10%	15%	7%	9%	45	$1,125	22.2%	26.7%
9	G-2	GRASS-2	ZOYSIA	PLANT (FLAT)	10	$50	9%	10	5	20	10	$30	$25	$10	$50	10%	15%	7%	9%	45	$1,125	22.2%	44.4%
10	G-2	GRASS-2	LEMON	PLANT (FLAT)	20	$10	7%	10	5	20	10	$30	$25	$10	$50	10%	15%	7%	9%	45	$1,125	44.4%	17.8%
Sheet1

Cell Formulas
Range	Formula
H3:H10	H3	=FILTER([OH QTY],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BERMUDA")))
I3:I10	I3	=FILTER([OH QTY],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BLUE")))
J3:J10	J3	=FILTER([OH QTY],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="LEMON")))
K3:K10	K3	=FILTER([OH QTY],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="ZOYSIA")))
L3:L10	L3	=FILTER([LIST/UNIT],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BERMUDA")))
M3:M10	M3	=FILTER([LIST/UNIT],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BLUE")))
N3:N10	N3	=FILTER([LIST/UNIT],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="LEMON")))
O3:O10	O3	=FILTER([LIST/UNIT],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="ZOYSIA")))
P3:P10	P3	=FILTER([BULK PROMO],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BERMUDA")))
Q3:Q10	Q3	=FILTER([BULK PROMO],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="BLUE")))
R3:R10	R3	=FILTER([BULK PROMO],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="LEMON")))
S3:S10	S3	=FILTER([BULK PROMO],(([PROD CLASS]=[@[PROD CLASS]])*([CATEGORY]=[@CATEGORY])*([SUBTYPE]="ZOYSIA")))
T3:T10	T3	=SUM(CTC_TEST_CODE[@[BERM QTY]:[ZOY QTY]])
U3:U10	U3	=([@[BERM QTY]]*[@[BERM LSP]])+([@[BLUE QTY]]*[@[BLUE LSP]])+([@[LEM QTY]]*[@[LEM LSP]])+([@[ZOY QTY]]*[@[ZOY LSP]])
V3:V10	V3	=[@[OH QTY]]/[@[TTL VOL]]
W3:W10	W3	=([@[OH QTY]]*[@[LIST/UNIT]])/[@[TTL VALUE]]
A3:A10	A3	="G-"&(TEXTAFTER([@[PROD CLASS]],"-"))

 

[RoryA]

This seems to me like a weird thing to want to do in PQ, but I'd probably create the orange parts as a separate query using a pivot, then merge them with the blue parts on whatever the filter fields are.

 

[RICH937]

This seems to me like a weird thing to want to do in PQ, but I'd probably create the orange parts as a separate query using a pivot, then merge them with the blue parts on whatever the filter fields are.

 

[RICH937]

I have no issues with doing a merge, I just can't seem to get an unpivot/group/pivot to work.

 

[AlienSx]

This creates columns H through S. No pivot/unpivot/group/merge, sorry. Add final columns (T - W) yourself.

let
    // your original table with columns A-G
    Source = your_table,
    // all subtypes
    subtypes = List.Buffer(List.Distinct(Source[SUBTYPE])),
    // record with qty/lsp/dis of all combinations
    rec = Record.Combine(
        Table.ToList(
            Source, 
            (x) => Record.FromList(
                {[QTY = x{4}, LSP = x{5}, DIS = x{6}]}, 
                {x{1} & x{3} & x{2}}
            )
        )
    ),
    // add qty, lsp and dis columns
    to_list = Table.ToList(
        Source, 
        (x) => x & 
            List.Transform(subtypes, (w) => Record.FieldOrDefault(rec, x{1} & x{3} & w)[QTY]) & 
            List.Transform(subtypes, (w) => Record.FieldOrDefault(rec, x{1} & x{3} & w)[LSP]) & 
            List.Transform(subtypes, (w) => Record.FieldOrDefault(rec, x{1} & x{3} & w)[DIS])
    ),
    // transform back to table with new columns
    to_table = Table.FromList(
        to_list, 
        (x) => x,
        Table.ColumnNames(Source) & 
        List.Transform(subtypes, (w) => w & " " & "QTY") & 
        List.Transform(subtypes, (w) => w & " " & "LSP") & 
        List.Transform(subtypes, (w) => w & " " & "DIS")
    )
in
    to_table

 

[alex78]

Hi @RICH937 ,


Try - your original table with columns B-G,

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColName = {Table.ColumnNames(Source)} & {{"QTY","LSP","DIS"}} & {List.Distinct(Source[SUBTYPE])},
    Col_H_To_S = List.Accumulate(List.Zip({List.LastN(ColName{0},3),ColName{1}}), Source, (a,b)=> List.Accumulate(ColName{2}, a, (c,d)=> Table.AddColumn(c, d & " " &  b{1}, each 
                let Prod = Record.Field(_,ColName{0}{0}) in Table.Column(Table.SelectRows(Source, each Record.Field(_,ColName{0}{1}) = d and Record.Field(_,ColName{0}{0}) = Prod), b{0}){0}))),
    Col_T = Table.AddColumn(Col_H_To_S, "TTL VOL", each let Prod = Record.Field(_,ColName{0}{0}) in List.Sum(Table.Column(Table.SelectRows(Source, each Record.Field(_,ColName{0}{0}) = Prod), ColName{0}{3}))),
    Col_U = Table.AddColumn(Col_T, "TTL VALUE", each List.Sum(Table.AddColumn(Table.FromColumns(List.Split(Record.FieldValues(Record.SelectFields(_, List.Transform({6..13}, each Table.ColumnNames(Col_T){_}))),4)), "Custom", each [Column1] * [Column2])[Custom])),
    Col_V = Table.AddColumn(Col_U, "SUB % VOL", each Record.Field(_,ColName{0}{3}) / Record.Field(_,Table.ColumnNames(Col_U){18})),
    Col_W = Table.AddColumn(Col_V, "SUB % VALUE", each Record.Field(_,ColName{0}{3}) * Record.Field(_,ColName{0}{4}) / Record.Field(_,Table.ColumnNames(Col_U){19})),
    #"Final" = let x =  Table.AddColumn(Col_W, "PROD_ID", each Text.Start(Record.Field(_,ColName{0}{0}),1) & "-" & Text.End(Record.Field(_,ColName{0}{0}),1)) in Table.SelectColumns(x, {"PROD_ID"} & Table.ColumnNames(Col_W))
in
    #"Final"

Regards,

 

[alex78]

Regards,

 

[alex78]

Hi @RICH937,

Slightly optimized code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ColName = {Table.ColumnNames(Source)} & {{"QTY","LSP","DIS"}} & {List.Distinct(Source[SUBTYPE])},
    fx = (Prod_Class as text, Col_Name as text, Subtype as list) as number => 
                List.Sum(Table.Column(Table.SelectRows(Source, each List.Contains(Subtype,Record.Field(_,ColName{0}{1})) and Record.Field(_,ColName{0}{0}) = Prod_Class), Col_Name)),
    Col_H_To_S = List.Accumulate(List.Zip({List.LastN(ColName{0},3),ColName{1}}), Source, 
                 (a,b)=> List.Accumulate(ColName{2}, a,     
                 (c,d)=> Table.AddColumn(c, d & " " &  b{1}, 
                 each fx(Record.Field(_,ColName{0}{0}),b{0},{d})))),
    Col_T = Table.AddColumn(Col_H_To_S, "TTL VOL", 
                each fx(Record.Field(_,ColName{0}{0}), ColName{0}{3}, ColName{2})),
    Col_U = Table.AddColumn(Col_T, "TTL VALUE", 
                each let x = List.Zip(List.Split(Record.FieldValues(Record.SelectFields(_, List.Transform({6..13}, each Table.ColumnNames(Col_T){_}))),4)) 
                in List.Accumulate(x,0,(s,c)=> s + (c{0}*c{1}))),
    Col_V = Table.AddColumn(Col_U, "SUB % VOL", 
                each Record.Field(_,ColName{0}{3}) / Record.Field(_,Table.ColumnNames(Col_U){18})),
    Col_W = Table.AddColumn(Col_V, "SUB % VALUE", 
                each Record.Field(_,ColName{0}{3}) * Record.Field(_,ColName{0}{4}) / Record.Field(_,Table.ColumnNames(Col_U){19})),
    Col_A = let x =  Table.AddColumn(Col_W, "PROD_ID", each Text.Start(Record.Field(_,ColName{0}{0}),1) & "-" & Text.End(Record.Field(_,ColName{0}{0}),1)) 
                in Table.SelectColumns(x, {"PROD_ID"} & Table.ColumnNames(Col_W))
in
    Col_A

Regards,