SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillspower-bi-partitions

power-bi-partitions

Data & analytics

View repository

Updated 10-15 23:42

About this skill

Power BI Partitions & Expressions Skill

Manage table partitions, named expressions (M queries), and calendar tables.

Prerequisites

pipx install pbi-cli-tool
pbi-cli skills install
pbi connect

Partitions

Partitions define how data is loaded into a table. Each table has at least one partition.

# List partitions in a table
pbi partition list --table Sales
pbi --json partition list --table Sales

# Create a partition with an M expression
pbi partition create "Sales_2024" --table Sales \
  --expression "let Source = Sql.Database(\"server\", \"db\"), Sales = Source{[Schema=\"dbo\",Item=\"Sales\"]}[Data], Filtered = Table.SelectRows(Sales, each [Year] = 2024) in Filtered" \
  --mode Import

# Create a partition with DirectQuery mode
pbi partition create "Sales_Live" --table Sales --mode DirectQuery

# Delete a partition
pbi partition delete "Sales_Old" --table Sales

# Refresh a specific partition
pbi partition refresh "Sales_2024" --table Sales

Named Expressions

Named expressions are shared M/Power Query definitions used as data sources or reusable query logic.

# List all named expressions
pbi expression list
pbi --json expression list

# Get a specific expression
pbi expression get "ServerURL"
pbi --json expression get "ServerURL"

# Create a named expression (M query)
pbi expression create "ServerURL" \
  --expression '"https://api.example.com/data"' \
  --description "API endpoint for data refresh"

# Create a parameterized data source
pbi expression create "DatabaseServer" \
  --expression '"sqlserver.company.com"' \
  --description "Production database server name"

# Delete a named expression
pbi expression delete "OldSource"

Calendar Tables

Calendar/date tables enable time intelligence in DAX. Mark a table as a date table to unlock functions like TOTALYTD, SAMEPERIODLASTYEAR, etc.

# List all calendar/date tables
pbi calendar list
pbi --json calendar list

# Mark a table as a calendar table
pbi calendar mark Calendar --date-column Date

# Alternative: use the table command
pbi table mark-date Calendar --date-column Date

Workflow: Set Up Partitioned Table

# 1. Create a table
pbi table create Sales --mode Import

# 2. Create partitions for different date ranges
pbi partition create "Sales_2023" --table Sales \
  --expression "let Source = ... in Filtered2023" \
  --mode Import

pbi partition create "Sales_2024" --table Sales \
  --expression "let Source = ... in Filtered2024" \
  --mode Import

# 3. Refresh specific partitions
pbi partition refresh "Sales_2024" --table Sales

# 4. Verify partitions
pbi --json partition list --table Sales

Workflow: Manage Data Sources

# 1. List current data source expressions
pbi --json expression list

# 2. Create shared connection parameters
pbi expression create "ServerName" \
  --expression '"prod-sql-01.company.com"' \
  --description "Production SQL Server"

pbi expression create "DatabaseName" \
  --expression '"SalesDB"' \
  --description "Production database"

# 3. Verify
pbi --json expression list

Best Practices

  • Use partitions for large tables to enable incremental refresh
  • Refresh only the partitions that have new data (pbi partition refresh)
  • Use named expressions for shared connection parameters (server names, URLs)
  • Always mark calendar tables with pbi calendar mark for time intelligence
  • Use --json output for scripted partition management
  • Export model as TMDL to version-control partition definitions: pbi database export-tmdl ./model/

Gotchas

  • Incremental refresh needs RangeStart/RangeEnd parameters with specific names and DateTime type: The PBI service silently falls back to full refresh if the parameters are misnamed, typed as Date instead of DateTime, or missing the policy binding. The model loads fine — only the refresh metric reveals the bug, usually weeks later when storage costs spike.
  • partition refresh only refreshes the specified partition, but downstream calculated columns rebuild at the table level: If a partitioned fact table has calculated columns derived from another partition, you'll see inconsistent values until a --type Calculate runs.
  • Named expressions used as data sources must be referenced by name, not inlined: Inlining the connection string into multiple partitions creates separate data source records in PBI Service — each prompts for credentials separately on first refresh.
  • DirectQuery and Import partitions on the same table is illegal in Power BI: The CLI may accept mixed-mode partition creation in some versions but the model fails to refresh with a vague "composite model" error. Composite models require explicit dual-storage configuration on the table, not per-partition.
  • pbi expression create with a single quoted string value silently loses outer quotes: --expression '"https://x.com"' is correct (M needs quotes), but --expression "https://x.com" creates an invalid expression referencing an undefined identifier.
  • Calendar tables marked with pbi calendar mark cannot be unmarked via the CLI: Once marked, the only way to revert is editing the TMDL dataCategory directly or using Desktop's UI. Watch for this when a Calendar table needs to become a regular dimension.