Skip to content

Instantly share code, notes, and snippets.

@halbuki
Last active September 6, 2022 12:34
Show Gist options
  • Save halbuki/e934bf72bdd7c36775a1ee65eac596b6 to your computer and use it in GitHub Desktop.
Save halbuki/e934bf72bdd7c36775a1ee65eac596b6 to your computer and use it in GitHub Desktop.
Excel Lambda functions for Power Query Record functions
/* USE NAMESPACE "Record" */
Field = LAMBDA(_record, _field,
LET(
fieldnames, Record.FieldNames(_record),
fieldcol, List.PositionOf(fieldnames, _field),
INDEX(_record, 2, fieldcol)
)
);
FieldCount = LAMBDA(_record, COLUMNS(_record));
FieldNames = LAMBDA(_record,
TRANSPOSE(INDEX(_record, 1, 0))
);
HasFields = LAMBDA(_record, _fields,
LET(
fieldnames, Record.FieldNames(_record),
MAKEARRAY(
COUNTA(ISERROR(_fields)),
1,
LAMBDA(ir, ic,
LET(
_field, INDEX(_fields, ir),
NOT(ISERROR(List.PositionOf(fieldnames, _field)))
)
)
)
)
);
SelectFields = LAMBDA(_record, _fields,
LET(
fieldnames, Record.FieldNames(_record),
MAKEARRAY(
2,
COUNTA(ISERROR(_fields)),
LAMBDA(ir, ic,
LET(
colname, INDEX(_fields, ic),
colpos, List.PositionOf(fieldnames, colname),
CHOOSE(ir, INDEX(_fields, ic), INDEX(_record, 2, colpos))
)
)
)
)
);
ToList = LAMBDA(_record, TRANSPOSE(INDEX(_record, 2, 0)));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment