DataQuery.inAI
90+ Pre-Configured Functions

The DataQuery Function Library

Search curated transformations across Python (pandas), DuckDB SQL, and Power BI DAX. Click to test on your own data instantly.

PYTHONbasic

df.head(n)

Return the first n rows of the dataframe.

df.head(5)
PYTHONbasic

df.tail(n)

Return the last n rows of the dataframe.

df.tail(5)
PYTHONbasic

df.info()

Summary of column datatypes and memory usage.

df.info()
PYTHONbasic

df.describe()

Summary statistics (mean, std, min, max, percentiles).

df.describe()
PYTHONbasic

df.shape

Tuple showing total row and column dimensions.

print(df.shape)
PYTHONbasic

df.columns.tolist()

List of all column identifiers.

df.columns.tolist()
PYTHONbasic

df.rename()

Rename one or multiple dataset columns.

df.rename(columns={'revenue': 'total_revenue'})
PYTHONbasic

df.drop(columns=[])

Drop specified columns from the active view.

df.drop(columns=['unneeded_col'])
PYTHONbasic

df.dropna()

Eliminate rows containing missing/null values.

df.dropna(subset=['revenue'])
PYTHONbasic

df.fillna()

Impute missing null values with constant or median.

df['profit'].fillna(0)
PYTHONintermediate

df.groupby().agg()

Aggregate metric sums and averages by categorical dimensions.

df.groupby('region')['revenue'].sum().reset_index()
PYTHONintermediate

df.sort_values()

Sort records ascending or descending by target metrics.

df.sort_values(by='revenue', ascending=False)
PYTHONintermediate

Calculated Ratio

Create computed column dividing two numeric fields.

df['margin_pct'] = df['profit'] / df['revenue']
PYTHONintermediate

pd.to_datetime()

Parse date string representations into datetime objects.

df['order_date'] = pd.to_datetime(df['order_date'])
PYTHONintermediate

df.query()

Filter dataframe rows via concise string expressions.

df.query('revenue > 10000 & region == "Europe"')
PYTHONintermediate

df['col'].value_counts()

Distribution frequencies of categorical attributes.

df['category'].value_counts()
PYTHONintermediate

df.drop_duplicates()

Remove duplicate entity rows by key identifier.

df.drop_duplicates(subset=['order_id'])
PYTHONintermediate

pd.merge()

Join two tables with SQL-like left or inner join.

pd.merge(df1, df2, on='customer_id', how='left')
PYTHONintermediate

df.pivot_table()

Cross-tabulation pivot matrix of values.

df.pivot_table(index='region', columns='category', values='revenue', aggfunc='sum')
PYTHONintermediate

df.assign()

Chain assignment of multiple computed columns.

df.assign(tax=lambda x: x.revenue * 0.15)
PYTHONadvanced

Rolling 7-Day Window

Moving average smoother over chronological series.

df.rolling(window=7, on='order_date')['revenue'].mean()
PYTHONadvanced

Inter-Order Cycle Diff

Calculate day intervals between chronological transactions.

df.groupby('customer_tier')['order_date'].diff().dt.days
PYTHONadvanced

pd.qcut() Quantile Binning

Segment dataset into statistical quintiles (RFM analysis).

pd.qcut(df['revenue'], q=5, labels=['Very Low','Low','Med','High','Top'])
PYTHONadvanced

df.corr() Pearson Matrix

Correlation coefficients between numeric metrics.

df.corr(numeric_only=True)
PYTHONadvanced

Z-Score Anomaly Detector

Identify statistical outliers exceeding 3 standard deviations.

np.where((df['revenue'] - df['revenue'].mean()) / df['revenue'].std() > 3, 'Outlier', 'Normal')
PYTHONadvanced

df.pct_change()

Period-over-period percentage expansion.

df['growth_pct'] = df['revenue'].pct_change()
PYTHONadvanced

pd.melt() Wide-to-Long

Unpivot wide matrix attributes into tidy key-value pairs.

pd.melt(df, id_vars=['region'], value_vars=['revenue', 'cost'])
PYTHONadvanced

df.explode()

Explode nested arrays into independent rows.

df.explode('tags')
PYTHONadvanced

pd.crosstab()

Contingency frequency matrix for two categories.

pd.crosstab(df['region'], df['category'])
PYTHONadvanced

np.select() Case Multi-Branch

Vectorized case statement evaluations.

np.select([df.revenue > 20000], ['Tier 1'], default='Tier 2')
SQLbasic

SELECT Preview

Preview top records from current table.

SELECT * FROM sales LIMIT 10;
SQLbasic

COUNT(*) Aggregation

Count volume of records in table.

SELECT COUNT(*) AS total_rows FROM sales;
SQLbasic

SELECT DISTINCT

List distinct categorical values.

SELECT DISTINCT region FROM sales;
SQLbasic

WHERE Conditional Filter

Filter rows matching numeric or text predicates.

SELECT * FROM sales WHERE revenue > 10000;
SQLbasic

ORDER BY Sort

Order output records descending by revenue.

SELECT * FROM sales ORDER BY revenue DESC;
SQLbasic

GROUP BY Summation

Group records and sum numerical attributes.

SELECT region, SUM(revenue) AS total_rev FROM sales GROUP BY 1;
SQLbasic

AVG() Calculation

Calculate arithmetic mean of column.

SELECT AVG(profit) AS mean_profit FROM sales;
SQLbasic

MIN() & MAX() Bounds

Identify boundary values of numerical metric.

SELECT MIN(revenue) AS min_rev, MAX(revenue) AS max_rev FROM sales;
SQLbasic

UPPER() / LOWER()

Transform string casing for normalization.

SELECT UPPER(category) FROM sales;
SQLbasic

COALESCE() Null Fallback

Return fallback value if field is null.

SELECT COALESCE(customer_tier, 'Unclassified') FROM sales;
SQLintermediate

DATE_TRUNC('month')

Bucket chronological records into monthly intervals.

SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) FROM sales GROUP BY 1;
SQLintermediate

HAVING Threshold Filter

Filter post-aggregation metrics.

SELECT category, SUM(revenue) FROM sales GROUP BY 1 HAVING SUM(revenue) > 50000;
SQLintermediate

CASE WHEN Logic

Conditional branch classification in SQL.

SELECT order_id, CASE WHEN revenue > 15000 THEN 'VIP' ELSE 'Standard' END AS status FROM sales;
SQLintermediate

ROUND() Decimals

Format float metric to two decimal places.

SELECT ROUND(profit / revenue * 100, 2) AS margin_pct FROM sales;
SQLintermediate

DATEDIFF('day')

Calculate elapsed calendar days between two dates.

SELECT DATEDIFF('day', start_date, end_date) FROM sales;
SQLintermediate

CONCAT() String Merge

Join multiple strings into composite identifier.

SELECT CONCAT(region, ' - ', category) AS segment FROM sales;
SQLintermediate

CAST() Type Conversion

Cast value to explicit numeric or varchar type.

SELECT CAST(revenue AS INTEGER) FROM sales;
SQLintermediate

REGEXP_MATCHES()

DuckDB regex pattern evaluator.

SELECT * FROM sales WHERE REGEXP_MATCHES(order_id, '^ORD-[0-9]+');
SQLintermediate

COUNT(DISTINCT entity)

Count unique occurrences of categorical entities.

SELECT category, COUNT(DISTINCT customer_tier) FROM sales GROUP BY 1;
SQLintermediate

Subquery Filter

Filter records using results of nested query.

SELECT * FROM sales WHERE revenue > (SELECT AVG(revenue) FROM sales);
SQLadvanced

SUM() OVER Running Total

Cumulative running total partitioned by region.

SUM(revenue) OVER (PARTITION BY region ORDER BY order_date) AS running_rev
SQLadvanced

ROW_NUMBER() Ranking

Assign sequential ranking for deduplication.

ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank
SQLadvanced

LAG() Prior Period Delta

Fetch previous row value to compute period growth.

LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_rev
SQLadvanced

NTILE(4) Quartile Buckets

Divide dataset into four even statistical quartiles.

NTILE(4) OVER (ORDER BY revenue) AS quartile
SQLadvanced

WITH CTE Modular Pipeline

Common Table Expression for readable transformations.

WITH regional_rev AS (SELECT region, SUM(revenue) AS total FROM sales GROUP BY 1) SELECT * FROM regional_rev;
SQLadvanced

QUALIFY Deduplication

DuckDB native window filter without nested subquery.

QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) = 1
SQLadvanced

APPROX_COUNT_DISTINCT()

HyperLogLog cardinality estimator for massive datasets.

SELECT APPROX_COUNT_DISTINCT(order_id) FROM sales;
SQLadvanced

MEDIAN() Exact Centroid

Calculate exact statistical 50th percentile.

SELECT MEDIAN(revenue) FROM sales;
SQLadvanced

DuckDB PIVOT Statement

Native SQL pivoting wide transformation.

PIVOT sales ON category USING SUM(revenue);
SQLadvanced

Ratio to Total OVER()

Compute each row's percentage contribution to grand total.

revenue / SUM(revenue) OVER () * 100 AS pct_of_total
DAXbasic

Total Sales = SUM()

Grand sum of column metric.

Total Sales = SUM(Sales[Revenue])
DAXbasic

Average Order Value = AVERAGE()

Mean order size.

AOV = AVERAGE(Sales[Revenue])
DAXbasic

Total Orders = COUNTROWS()

Count records in active table view.

Total Orders = COUNTROWS(Sales)
DAXbasic

Distinct Entities = DISTINCTCOUNT()

Count unique customer IDs.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])
DAXbasic

Minimum Bound = MIN()

Minimum value in field.

Min Deal = MIN(Sales[Revenue])
DAXbasic

Maximum Bound = MAX()

Maximum value in field.

Max Deal = MAX(Sales[Revenue])
DAXbasic

Safe Margin % = DIVIDE()

Safe division guarded against zero denominators.

Margin % = DIVIDE([Total Profit], [Total Sales], 0)
DAXbasic

Profit = SUM(Sales[Profit])

Total profit summation.

Total Profit = SUM(Sales[Profit])
DAXbasic

Order Year = YEAR()

Extract calendar year.

Order Year = YEAR(Sales[OrderDate])
DAXbasic

Order Month = MONTH()

Extract calendar month index.

Order Month = MONTH(Sales[OrderDate])
DAXintermediate

US Sales = CALCULATE()

Modify filter context for specific region.

US Sales = CALCULATE([Total Sales], Sales[Region] = "North America")
DAXintermediate

High Value Orders = FILTER()

Iterate table and filter records > threshold.

High Deals = COUNTROWS(FILTER(Sales, Sales[Revenue] > 10000))
DAXintermediate

All Regions = ALL()

Remove regional filter to compute denominator for shares.

All Region Sales = CALCULATE([Total Sales], ALL(Sales[Region]))
DAXintermediate

Sales Share % = DIVIDE([Total], [All])

Calculate percentage contribution to global total.

Regional Share % = DIVIDE([Total Sales], [All Region Sales])
DAXintermediate

Sales YTD = TOTALYTD()

Cumulative year-to-date time intelligence.

Sales YTD = TOTALYTD([Total Sales], Dates[Date])
DAXintermediate

Sales QTD = TOTALQTD()

Quarter-to-date aggregation.

Sales QTD = TOTALQTD([Total Sales], Dates[Date])
DAXintermediate

Sales MTD = TOTALMTD()

Month-to-date aggregation.

Sales MTD = TOTALMTD([Total Sales], Dates[Date])
DAXintermediate

Sales SPLY = SAMEPERIODLASTYEAR()

Prior year comparison for variance analysis.

Sales Prior Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
DAXintermediate

YoY Growth = [Total] - [Prior Year]

Absolute variance compared to previous year.

YoY Growth = [Total Sales] - [Sales Prior Year]
DAXintermediate

YoY Growth % = DIVIDE(YoY, Prior)

Percentage growth rate compared to prior year.

YoY Growth % = DIVIDE([YoY Growth], [Sales Prior Year])
DAXadvanced

SUMX() Iterative Context

Compute row-by-row expressions before sum.

Revenue Gross = SUMX(Sales, Sales[Units] * Sales[UnitPrice])
DAXadvanced

Top 5 Deals = TOPN()

Filter table to the top N performers.

Top 5 Sales = CALCULATE([Total Sales], TOPN(5, Customers, [Total Sales]))
DAXadvanced

30-Day Rolling = DATESINPERIOD()

Rolling 30-day window aggregation.

Sales 30D = CALCULATE([Total Sales], DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -30, DAY))
DAXadvanced

Customer Tier = IF(Nested)

Classify customers into Enterprise, Mid-Market, SMB.

Tier = IF([Total Sales] > 20000, "Enterprise", IF([Total Sales] > 5000, "Mid-Market", "SMB"))
DAXadvanced

Active Customers 90D

Count customers active within last 90 calendar days.

Active 90D = CALCULATE(DISTINCTCOUNT(Sales[CustomerID]), DATESINPERIOD(Dates[Date], LASTDATE(Dates[Date]), -90, DAY))
DAXadvanced

Churn Delta = Total - Active

Dormant accounts calculation.

Churned Accounts = [Total Customers] - [Active 90D]
DAXadvanced

ALLSELECTED() Dynamic Baseline

Compute ratio against currently sliced dashboard selections.

Selected Total = CALCULATE([Total Sales], ALLSELECTED(Sales))
DAXadvanced

RANKX() Product Ranking

Dense ranking of entities by metric value.

Rank = RANKX(ALL(Products[Category]), [Total Sales], , DESC, Dense)
DAXadvanced

MEDIANX() Iterative Median

Iterative median transaction evaluation.

Median Deal = MEDIANX(Sales, [Total Sales])
DAXadvanced

Cumulative % Pareto Curve

Pareto 80/20 distribution calculation.

Cumulative % = DIVIDE(CALCULATE([Total Sales], FILTER(ALL(Products), [Rank] <= EARLIER([Rank]))), [All Product Sales])