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

RICH937 · May 14, 2024

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%

4

GRASS-1

ZOYSIA

SEED (BAG)

45

$750

18%

5

GRASS-2

BLUE

PLANT (FLAT)

5

$25

15%

6

GRASS-1

LEMON

SEED (BAG)

15

$225

15%

7

GRASS-1

BLUE

SEED (BAG)

100

$75

12%

8

GRASS-2

BERMUDA

PLANT (FLAT)

10

$30

10%

9

GRASS-2

ZOYSIA

PLANT (FLAT)

10

$50

9%

10

GRASS-2

LEMON

PLANT (FLAT)

20

$10

7%

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 · May 14, 2024

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 · May 14, 2024

RoryA said:
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 · May 14, 2024

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

AlienSx · May 15, 2024

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

Power Query:

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 · May 15, 2024

Hi @RICH937 ,

Try - your original table with columns B-G,

Power Query:

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 · May 15, 2024

Regards,

alex78 · May 17, 2024

Hi @RICH937,

Slightly optimized code

Python:

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,

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

RICH937

Board Regular

Excel Facts

RoryA

MrExcel MVP, Moderator

RICH937

Board Regular

RICH937

Board Regular

AlienSx

New Member

alex78

New Member

alex78

New Member

alex78

New Member

Forum statistics

Share this page

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

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

Board Regular

New Member

New Member

New Member

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock