Table.*

Power Query Table.* functions on Spark DataFrames.

class fabrictools.powerquery.table.Table[source]

Bases: object

Namespace for Power Query Table.* functions on Spark DataFrames.

Column names are resolved like fabrictools.resolve_dataframe_column() (physical, normalized, or snake_case labels).

Example

>>> from fabrictools import read_lakehouse, Table, List
>>> df = read_lakehouse("Lakehouse", "dbo/customer_projects")
>>> df = Table.Group(df, ["RAO CODE"], [("Amount", "AMOUNT CNY", List.Sum)])

static AddColumn(df: pyspark.sql.DataFrame, name: str, generator: pyspark.sql.Column | Callable[[], pyspark.sql.Column]) → pyspark.sql.DataFrame[source]

Add a computed column (Power Query Table.AddColumn).

Parameters:

df (DataFrame) – Input dataframe.
name (str) – Name of the new column.
generator (Column | collections.abc.Callable[[], Column]) – Spark Column expression or zero-argument callable returning one.

Returns:

Dataframe with the added column.

Return type:

DataFrame

Example

>>> from fabrictools import Table, Date
>>> from pyspark.sql import functions as F
>>> df = Table.AddColumn(df, "Year", Date.Year(F.col("Date of Revenue recognition")))

static Buffer(df: pyspark.sql.DataFrame) → pyspark.sql.DataFrame[source]

Cache a table in memory and disk (Power Query Table.Buffer).

Parameters:: df (DataFrame) – Input dataframe.
Returns:: Persisted dataframe (same object, cached for reuse).
Return type:: DataFrame

Example

>>> from fabrictools import Table
>>> cached = Table.Buffer(df)

static Combine(tables: Sequence[pyspark.sql.DataFrame]) → pyspark.sql.DataFrame[source]

Union tables by column name (Power Query Table.Combine).

Parameters:: tables (collections.abc.Sequence[DataFrame]) – Dataframes to stack vertically.
Returns:: Combined dataframe.
Return type:: DataFrame
Raises:: ValueError – If tables is empty.

Example

>>> from fabrictools import Table
>>> combined = Table.Combine([df_2024, df_2025])

static Distinct(df: pyspark.sql.DataFrame, columns: Sequence[str] | None = None) → pyspark.sql.DataFrame[source]

Remove duplicate rows (Power Query Table.Distinct).

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str] | None) – Optional subset of columns to test for duplication; all columns if omitted.

Returns:

Deduplicated dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.Distinct(df, ["RAO CODE", "END USER"])

static DuplicateColumn(df: pyspark.sql.DataFrame, column: str, new_column: str) → pyspark.sql.DataFrame[source]

Duplicate a column (Power Query Table.DuplicateColumn).

Parameters:

df (DataFrame) – Input dataframe.
column (str) – Source column name.
new_column (str) – Name of the copy.

Returns:

Dataframe with the duplicated column.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.DuplicateColumn(df, "Project No.", "Project No. backup")

static FillDown(df: pyspark.sql.DataFrame, columns: Sequence[str]) → pyspark.sql.DataFrame[source]

Propagate last non-null value downward (Power Query Table.FillDown).

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str]) – Columns to fill.

Returns:

Dataframe with nulls filled from above.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.FillDown(df, ["Category", "Subcategory"])

static FillUp(df: pyspark.sql.DataFrame, columns: Sequence[str]) → pyspark.sql.DataFrame[source]

Propagate next non-null value upward (Power Query Table.FillUp).

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str]) – Columns to fill.

Returns:

Dataframe with nulls filled from below.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.FillUp(df, ["Category"])

static FirstN(df: pyspark.sql.DataFrame, count: int) → pyspark.sql.DataFrame[source]

Return first N rows (Power Query Table.FirstN).

Parameters:

df (DataFrame) – Input dataframe.
count (int) – Number of rows to keep.

Returns:

First count rows.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> preview = Table.FirstN(df, 100)

static Group(df: pyspark.sql.DataFrame, keys: str | Sequence[str] | set, aggregations: Sequence[tuple[str, str, str]]) → pyspark.sql.DataFrame[source]

Group rows and aggregate columns (Power Query Table.Group).

Parameters:

df (DataFrame) – Input dataframe.
keys (str | collections.abc.Sequence[str] | set) – Group key column name(s).
aggregations (collections.abc.Sequence[tuple[str, str, str]]) – (output_name, source_column, List.Sum|Max|...) tuples.

Returns:

Grouped and aggregated dataframe.

Return type:

DataFrame

Raises:

ValueError – If no key column resolves on df.

Example

>>> from fabrictools import Table, List
>>> df = Table.Group(df, {"RAO CODE"}, [
...     ("Client", "END USER", List.Max),
...     ("Amount (Adjusted)", "OI ADJUSTED", List.Sum),
... ])

static Join(left: pyspark.sql.DataFrame, right: pyspark.sql.DataFrame, join_keys: Sequence[str], *, how: str = 'left') → pyspark.sql.DataFrame[source]

Simple join alias (Power Query Table.Join).

Parameters:

left (DataFrame) – Left dataframe.
right (DataFrame) – Right dataframe.
join_keys (collections.abc.Sequence[str]) – Key column names (same on both sides).
how (str) – Spark join type.

Returns:

Joined dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.Join(orders, products, ["sku"], how="inner")

static LastN(df: pyspark.sql.DataFrame, count: int) → pyspark.sql.DataFrame[source]

Return last N rows (Power Query Table.LastN).

Parameters:

df (DataFrame) – Input dataframe.
count (int) – Number of rows to keep from the end.

Returns:

Last count rows.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> tail = Table.LastN(df, 50)

static NestedJoin(left: pyspark.sql.DataFrame, right: pyspark.sql.DataFrame, join_keys: Sequence[str], *, how: str = 'left', left_keys: Sequence[str] | None = None, right_keys: Sequence[str] | None = None) → pyspark.sql.DataFrame[source]

Join two tables (Power Query Table.NestedJoin).

Parameters:

left (DataFrame) – Left dataframe.
right (DataFrame) – Right dataframe.
join_keys (collections.abc.Sequence[str]) – Column names used on both sides when left_keys / right_keys omitted.
how (str) – Spark join type (left, inner, right, outer).
left_keys (collections.abc.Sequence[str] | None) – Optional left-side key column names.
right_keys (collections.abc.Sequence[str] | None) – Optional right-side key column names.

Returns:

Joined dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.NestedJoin(orders, customers, ["customer_id"], how="left")

static Pivot(df: pyspark.sql.DataFrame, group_columns: Sequence[str], pivot_column: str, value_column: str, *, agg: str = 'sum') → pyspark.sql.DataFrame[source]

Pivot a table (Power Query Table.Pivot).

Wraps fabrictools.build_tcd().

Parameters:

df (DataFrame) – Input dataframe.
group_columns (collections.abc.Sequence[str]) – Row grouping columns.
pivot_column (str) – Column whose distinct values become new columns.
value_column (str) – Column to aggregate.
agg (str) – Aggregation name (sum, max, avg, …).

Returns:

Pivoted dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> wide = Table.Pivot(df, ["Region"], "Year", "Sales", agg="sum")

static Range(df: pyspark.sql.DataFrame, offset: int, count: int) → pyspark.sql.DataFrame[source]

Return a slice of rows (Power Query Table.Range).

Parameters:

df (DataFrame) – Input dataframe.
offset (int) – Number of rows to skip.
count (int) – Number of rows to return after the offset.

Returns:

Row slice [offset, offset + count).

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> page = Table.Range(df, offset=20, count=10)

static RemoveColumns(df: pyspark.sql.DataFrame, columns: Sequence[str]) → pyspark.sql.DataFrame[source]

Remove columns (Power Query Table.RemoveColumns).

Delegates to fabrictools.remove_columns().

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str]) – Column names to drop.

Returns:

Dataframe without the listed columns.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.RemoveColumns(df, ["Backlog 2024", "Turnover 2021"])

static RenameColumns(df: pyspark.sql.DataFrame, renames: Sequence[tuple[str, str]] | dict[str, str]) → pyspark.sql.DataFrame[source]

Rename columns (Power Query Table.RenameColumns).

Parameters:

df (DataFrame) – Input dataframe.
renames (collections.abc.Sequence[tuple[str, str]] | dict[str, str]) – (old_name, new_name) pairs or mapping dict.

Returns:

Dataframe with renamed columns.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.RenameColumns(df, [
...     ("Amount (Adjusted)", "Total Contract (Adjusted)"),
...     ("Amount", "Total Contract"),
... ])

static ReorderColumns(df: pyspark.sql.DataFrame, columns: Sequence[str]) → pyspark.sql.DataFrame[source]

Move columns to the front; keep unlisted columns at the end (Power Query Table.ReorderColumns).

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str]) – Column names to move to the front, in order.

Returns:

Reordered dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.ReorderColumns(df, ["Year", "Project No.", "Turnover"])

static ReplaceErrorValues(df: pyspark.sql.DataFrame, columns: Sequence[str], replacement: Any) → pyspark.sql.DataFrame[source]

Replace null values after failed transforms (Power Query Table.ReplaceErrorValues).

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str]) – Columns to scan for nulls.
replacement (Any) – Value to substitute when null.

Returns:

Dataframe with nulls replaced.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.ReplaceErrorValues(df, ["amount"], 0)

static ReplaceValue(df: pyspark.sql.DataFrame, old_value: Any, new_value: Any, columns: Sequence[str]) → pyspark.sql.DataFrame[source]

Replace values in columns (Power Query Table.ReplaceValue).

Parameters:

df (DataFrame) – Input dataframe.
old_value (Any) – Value to replace; use None to match null cells.
new_value (Any) – Replacement value.
columns (collections.abc.Sequence[str]) – Columns to update.

Returns:

Dataframe with replaced values.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.ReplaceValue(df, None, "External", ["Interco"])

static SelectColumns(df: pyspark.sql.DataFrame, columns: Sequence[str]) → pyspark.sql.DataFrame[source]

Keep only the listed columns in order (Power Query Table.SelectColumns).

Parameters:

df (DataFrame) – Input dataframe.
columns (collections.abc.Sequence[str]) – Column names to keep, in desired order.

Returns:

Projected dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.SelectColumns(df, ["Date", "Year", "RAO CODE", "Client"])

static SelectRows(df: DataFrame, predicate: Column | None = None, *, not_null: Sequence[str] | None = None, any_not_null: Sequence[str] | None = None) → DataFrame[source]

Filter rows (Power Query Table.SelectRows).

Pass a Spark boolean predicate, or use not_null (AND) / any_not_null (OR) shorthands for common [col] <> null patterns.

Parameters:

df (DataFrame) – Input dataframe.
predicate (Column | None) – Optional Spark boolean column expression.
not_null (collections.abc.Sequence[str] | None) – Keep rows where all listed columns are non-null.
any_not_null (collections.abc.Sequence[str] | None) – Keep rows where at least one listed column is non-null.

Returns:

Filtered dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.SelectRows(df, not_null=["Project Number"])
>>> df = Table.SelectRows(df, any_not_null=["BUYER", "RAO CODE"])

static Skip(df: pyspark.sql.DataFrame, count: int) → pyspark.sql.DataFrame[source]

Skip first N rows (Power Query Table.Skip).

Parameters:

df (DataFrame) – Input dataframe.
count (int) – Number of rows to skip from the top.

Returns:

Dataframe without the first count rows.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.Skip(raw_df, 9)

static Sort(df: pyspark.sql.DataFrame, sort_order: Sequence[tuple[str, bool]]) → pyspark.sql.DataFrame[source]

Sort rows (Power Query Table.Sort).

Parameters:

df (DataFrame) – Input dataframe.
sort_order (collections.abc.Sequence[tuple[str, bool]]) – Sequence of (column, Order.Ascending|Order.Descending) pairs.

Returns:

Sorted dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table, Order
>>> df = Table.Sort(df, [("Year", Order.Ascending), ("Date", Order.Ascending)])

static SplitColumn(df: pyspark.sql.DataFrame, column: str, delimiter: str, new_column_names: Sequence[str]) → pyspark.sql.DataFrame[source]

Split a column by delimiter (Power Query Table.SplitColumn).

Parameters:

df (DataFrame) – Input dataframe.
column (str) – Column to split.
delimiter (str) – Split delimiter (regex-escaped by Spark split).
new_column_names (collections.abc.Sequence[str]) – Names for each resulting part (by index).

Returns:

Dataframe with split columns added.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> df = Table.SplitColumn(df, "full_name", " ", ["first", "last"])

static TransformColumnTypes(df: pyspark.sql.DataFrame, type_map: dict[str, Any]) → pyspark.sql.DataFrame[source]

Cast column types (Power Query Table.TransformColumnTypes).

Accepts Power Query type tokens (type.text, Percentage.Type, Int64.Type) and delegates to fabrictools.cast_columns().

Parameters:

df (DataFrame) – Input dataframe.
type_map (dict[str, Any]) – Mapping {column_name: pq_type}.

Returns:

Dataframe with cast columns.

Return type:

DataFrame

Example

>>> from fabrictools import Table, Percentage
>>> df = Table.TransformColumnTypes(df, {"% Completion": Percentage.Type})

static TransformColumns(df: pyspark.sql.DataFrame, transforms: Sequence[tuple[str, Callable[[pyspark.sql.Column], pyspark.sql.Column]]]) → pyspark.sql.DataFrame[source]

Apply per-column transformers (Power Query Table.TransformColumns).

Parameters:

df (DataFrame) – Input dataframe.
transforms (collections.abc.Sequence[tuple[str, collections.abc.Callable]]) – (column_name, transformer) pairs; transformer receives a Column and returns a new Column.

Returns:

Transformed dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table, Number
>>> df = Table.TransformColumns(df, [
...     ("Total Invoice amount without VAT", Number.FromText),
... ])

static Unpivot(df: pyspark.sql.DataFrame, id_columns: Sequence[str], value_columns: Sequence[str], attribute_column: str = 'Attribute', value_column: str = 'Value') → pyspark.sql.DataFrame[source]

Unpivot columns to rows (Power Query Table.Unpivot).

Parameters:

df (DataFrame) – Input dataframe.
id_columns (collections.abc.Sequence[str]) – Identifier columns to keep fixed.
value_columns (collections.abc.Sequence[str]) – Wide columns to melt into rows.
attribute_column (str) – Name of the column holding former column names.
value_column (str) – Name of the column holding cell values.

Returns:

Long-format dataframe.

Return type:

DataFrame

Example

>>> from fabrictools import Table
>>> long_df = Table.Unpivot(
...     df, ["id"], ["Turnover 2023", "Turnover 2024"],
...     attribute_column="Year", value_column="Turnover",
... )