Skip to content

Instantly share code, notes, and snippets.

@ncalm

ncalm/example-data.txt

Created October 29, 2024 21:13
Show Gist options
  • Save ncalm/d1d4d2dc494540ad822f6af72475a6a6 to your computer and use it in GitHub Desktop.
Save ncalm/d1d4d2dc494540ad822f6af72475a6a6 to your computer and use it in GitHub Desktop.
Download ZIP
Get field members
Raw
example-data.txt
{"OrderDate","Category","SubCategory","Product","SalesAmount","OrderQuantity";40541,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40541,"Bikes","Road Bikes","Road-650 Black, 62",699.0982,1;40541,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40542,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40542,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40542,"Bikes","Mountain Bikes","Mountain-100 Black, 48",3374.99,1;40542,"Bikes","Mountain Bikes","Mountain-100 Silver, 38",3399.99,1;40543,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40543,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40543,"Bikes","Road Bikes","Road-650 Red, 52",699.0982,1;40543,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40543,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40544,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40544,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40545,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40545,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40545,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40545,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40545,"Bikes","Road Bikes","Road-650 Black, 62",699.0982,1;40546,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40546,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40546,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40546,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40547,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40547,"Bikes","Road Bikes","Road-650 Black, 58",699.0982,1;40547,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40548,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40548,"Bikes","Road Bikes","Road-650 Black, 58",699.0982,1;40548,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40549,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40549,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40549,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40549,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40549,"Bikes","Mountain Bikes","Mountain-100 Black, 44",3374.99,1;40549,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40550,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40550,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40550,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40551,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40551,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40551,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40551,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40552,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40552,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40552,"Bikes","Mountain Bikes","Mountain-100 Silver, 48",3399.99,1;40552,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40553,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40553,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40554,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40555,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40555,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40555,"Bikes","Road Bikes","Road-650 Black, 58",699.0982,1;40555,"Bikes","Mountain Bikes","Mountain-100 Black, 44",3374.99,1;40556,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40556,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40556,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40556,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40557,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40557,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40557,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40557,"Bikes","Mountain Bikes","Mountain-100 Silver, 44",3399.99,1;40558,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40558,"Bikes","Mountain Bikes","Mountain-100 Black, 44",3374.99,1;40559,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40559,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40559,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40559,"Bikes","Road Bikes","Road-650 Red, 52",699.0982,1;40559,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40559,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40559,"Bikes","Road Bikes","Road-150 Red, 52",3578.27,1;40559,"Bikes","Mountain Bikes","Mountain-100 Silver, 48",3399.99,1;40560,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40560,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40560,"Bikes","Mountain Bikes","Mountain-100 Silver, 42",3399.99,1;40560,"Bikes","Road Bikes","Road-650 Red, 44",699.0982,1;40561,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40561,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40561,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40561,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40562,"Bikes","Road Bikes","Road-150 Red, 62",3578.27,1;40562,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40562,"Bikes","Road Bikes","Road-150 Red, 44",3578.27,1;40562,"Bikes","Mountain Bikes","Mountain-100 Silver, 38",3399.99,1;40562,"Bikes","Mountain Bikes","Mountain-100 Black, 48",3374.99,1;40562,"Bikes","Mountain Bikes","Mountain-100 Black, 48",3374.99,1;40562,"Bikes","Mountain Bikes","Mountain-100 Silver, 38",3399.99,1;40562,"Bikes","Road Bikes","Road-150 Red, 56",3578.27,1;40562,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1;40562,"Bikes","Mountain Bikes","Mountain-100 Silver, 38",3399.99,1;40562,"Bikes","Mountain Bikes","Mountain-100 Silver, 48",3399.99,1;40563,"Bikes","Road Bikes","Road-150 Red, 48",3578.27,1}
Raw
excel-lambda-getfieldmembers.txt
GETTABLECOLUMN = LAMBDA(tbl, field, [withheaders],
DROP(FILTER(tbl, TAKE(tbl,1)=field),IF(withheaders=0,1,0))
);
GETFIELDMEMBERS = LAMBDA(tbl, fields,
DROP(
REDUCE(0,fields,
LAMBDA(a,b,
LET(
items, UNIQUE(GETTABLECOLUMN(tbl, b)),
VSTACK(a, HSTACK(EXPAND(b,ROWS(items),1,b), items))
)
)
),
1)
);
Raw
get_field_members.py
def get_field_members(tbl, fields):
return [
(field, item)
for field in fields
for item in tbl[field].unique()
]
# example usage:
get_field_members(xl("Table1[#All]", headers=True), xl("Table1[[#Headers],[Category]:[Product]]").iloc[0,:])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment