SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsspeckle-impl-powerbi

speckle-impl-powerbi

Design & creation

View repository

Updated 10-15 22:18

About this skill

speckle-impl-powerbi

Quick Reference

Connector Nature

The Power BI connector is fundamentally different from all other Speckle connectors. It is read-only and analytics-focused. It does NOT publish data back to Speckle. It loads Speckle model data as tabular rows for dashboards, reports, and 3D visualization within Power BI.

Aspect Power BI Connector Design Connectors (Revit, Rhino, etc.)
Direction Read-only (load) Bidirectional (publish + load)
Purpose Analytics, dashboards, visualization Model authoring, design exchange
Output Tabular data + 3D visual Native geometry in host app
Data modification NEVER modifies Speckle data Creates new versions on publish

Two Components

Component File Type Install Location Purpose
Power BI Connector .pqx %UserProfile%\Power BI Desktop\Custom Connectors\ Loads Speckle data as tables
3D Viewer Visual .pbiviz %UserProfile%\Power BI Desktop\Custom Visuals\ Renders 3D models in reports

Critical Warnings

NEVER assume the Power BI connector can publish data back to Speckle -- it is strictly read-only. Use design connectors (Revit, Rhino, etc.) for publishing.

NEVER skip Data Gateway setup when deploying reports to Power BI Service with scheduled refresh -- without a gateway, reports will NOT refresh automatically.

NEVER forget to enable third-party data sources in Power BI security settings -- the connector will NOT load without this configuration.

ALWAYS verify Speckle Desktop Services is running before using the connector -- the connector depends on this background process for authentication.

ALWAYS use @ in the model URL to pin a specific version -- without it, the connector loads the latest version on every refresh.

Installation

Automated Installation

  1. Download the Speckle Power BI connector from the official connectors page
  2. Run the installer -- it places .pqx and .pbiviz files automatically
  3. Configure Power BI security: File > Options and settings > Options > Security > Data Extensions > select "Allow any extension to load without validation or warning"
  4. Restart Power BI Desktop

Manual Installation

  1. Extract the downloaded ZIP archive
  2. Copy .pqx to %UserProfile%\Power BI Desktop\Custom Connectors\ (create folder if needed)
  3. Copy .pbiviz to %UserProfile%\Power BI Desktop\Custom Visuals\ (create folder if needed)
  4. Register the certificate thumbprint (requires administrator access):
    • Open Registry Editor (regedit)
    • Navigate to HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Power BI Desktop
    • Create TrustedCertificateThumbprints as REG_MULTI_SZ value
    • Add thumbprints: 5270B3C69218C25BE2F20AB2DD76429DDAA38F61 (v3.2.2 and earlier) and CDC489B709A709E3283568A9B75D75180B1355BE (v3.2.3 and later)
  5. Configure security settings as above, restart Power BI Desktop

Prerequisites

  • Power BI Desktop (Windows only)
  • Speckle Desktop Services running as background process (%APPDATA%\Speckle\Desktop Services\Speckle.Desktop.Services.exe)
  • Speckle account with access to target projects

Authentication

Initial Sign-In

  1. Select Get Data > search "Speckle" > Connect
  2. Paste model URL and select OK
  3. Browser authentication window opens automatically
  4. Sign in with Speckle account credentials
  5. Power BI caches credentials for future sessions

Switching Accounts

To switch Speckle accounts on the same server:

  1. Close Power BI Desktop completely
  2. Navigate to Power BI installation folder
  3. Delete CEF, WebView2, Cache folders and User.zip file
  4. Restart Power BI Desktop

Troubleshooting Authentication

Error Solution
"Access to resource is forbidden" Clear "Use my default web browser" in Options > Security > Authentication Browser
"Value cannot be null. Parameter name: uriString" Same fix as above
"Cannot connect to Desktop Service" Clear data source permissions, delete %APPDATA%/Speckle/Projects/PowerBITokenCache.db, restart
Desktop Service not running Launch %APPDATA%\Speckle\Desktop Services\Speckle.Desktop.Services.exe via Task Manager > Run new task

Loading Model Data

Basic Data Loading

  1. Copy model URL from the Speckle web app
  2. Select Get Data > Speckle > paste URL > OK
  3. Power BI imports the model as a tabular dataset
  4. Each row represents one Speckle object with its properties

Version Pinning

  • URL without @ symbol: ALWAYS loads the latest version on every refresh
  • URL with @ symbol (e.g., https://app.speckle.systems/projects/{id}/models/{id}@{versionId}): loads ONLY that specific version, ignoring subsequent updates

Refreshing Data

Select the Refresh button to reload with the latest version (unless version-pinned). For automatic refresh in Power BI Service, configure a Data Gateway.

3D Viewer Visual

Setup

  1. In the Visualizations pane, select the three dots (...)
  2. Choose "Import a visual from a file"
  3. Navigate to Documents/Power BI Desktop/Custom Visuals
  4. Select Speckle 3D Visual.pbiviz

Required Column Mappings

Field Required Purpose
Model Info YES Provides 3D geometry for rendering
Object IDs YES Enables element interactivity (click, hover)
Tooltip No Accepts any column for hover information display
Color by No Accepts any column for element coloring

Visual Controls

  • Ghost icon: Controls visibility of unselected elements -- toggled ON shows ghosted (translucent) elements, toggled OFF hides them completely
  • Logo visibility: Arrow in top bar hides Speckle branding (paid plans only)
  • Element selection: Click elements to select; selections filter other visuals on the report page
  • Tooltips: Hover over elements to display mapped column values

Updating the Visual

To apply a new version of the 3D visual: temporarily replace with a Table visual, then reselect Speckle 3D Visual and remap columns.

Helper Functions

All 7 helper functions are available in Power Query (M language). They are accessed via the formula bar or Advanced Editor.

Speckle.Projects.Issues

Speckle.Projects.Issues(url as text, getReplies as bool) as table

Fetches project issues from Speckle. Accepts project, model, or version URLs.

Parameter Type Required Description
url text YES Project, model, or version URL
getReplies bool No When true, extracts issue replies

Returns table with columns: Id, Title, Description, Status, Priority, Assignee, Due Date, Created, Updated, Labels, Replies, URL.

Speckle.Objects.Properties

Speckle.Objects.Properties(inputRecord as record, optional filterKeys as list) as record

Extracts object properties without navigating nested record structures.

Parameter Type Required Description
inputRecord record YES A single object record from the model table
filterKeys list No List of property keys to extract (omit for all)

Speckle.Objects.CompositeStructure

Speckle.Objects.CompositeStructure(inputRecord as record, optional outputAsList as nullable logical) as any

Extracts layered material structures from Walls, Floors, and Roofs. Works ONLY with Revit and Archicad data.

Parameter Type Required Description
inputRecord record YES A single object record
outputAsList logical No When true, returns list format for row expansion; default returns record

Speckle.Objects.MaterialQuantities

Speckle.Objects.MaterialQuantities(inputRecord as record, optional outputAsList as logical) as any

Accesses material quantities (volume, area per material) for a single object.

Parameter Type Required Description
inputRecord record YES A single object record
outputAsList logical No When true, returns list format for row expansion

Speckle.Models.MaterialQuantities

Speckle.Models.MaterialQuantities(inputTable as table, optional addPrefix as logical) as table

Expands material quantities across all rows in a table, creating columns per material.

Parameter Type Required Description
inputTable table YES The full model data table
addPrefix logical No When true, prefixes columns with "MQ." (e.g., "MQ.Concrete", "MQ.area")

Speckle.Models.Federate

Speckle.Models.Federate(tables as list, optional excludeData as logical) as table

Combines multiple model tables into a single federated table for the 3D visual.

Parameter Type Required Description
tables list YES List of model tables to combine
excludeData logical No When true, federates metadata only without full data import

Speckle.Utils.ExpandRecord

Speckle.Utils.ExpandRecord(table as table, columnName as text, optional FieldNames as list, optional UseCombinedNames as logical) as table

Expands record-type columns into separate table columns.

Parameter Type Required Description
table table YES Input table containing the record column
columnName text YES Name of the record column to expand
FieldNames list No Specific fields to expand (omit for all)
UseCombinedNames logical No When true, prefixes expanded columns with source column name

Data Gateway Configuration

A Data Gateway is required ONLY for scheduled refresh in Power BI Service. Basic connector usage and 3D visualization work without a gateway.

Personal Mode Gateway

Best for individual users with basic scheduled refresh needs.

  1. Download Power BI connector from Speckle connectors page
  2. Install "On-premises data gateway (personal mode)" from Microsoft
  3. Sign into the gateway application
  4. Verify gateway appears in Power BI Service: Settings > Manage connections and gateways
  5. Confirm Speckle connector appears in the Connectors tab
  6. Publish your report

Standard Mode Gateway

Best for organizations requiring multi-user access, DirectQuery support, and centralized management.

  1. Install the standard "On-premises data gateway"
  2. Sign into the gateway
  3. Locate Custom Connectors folder (typically C:\WINDOWS\ServiceProfiles\PBIEgwService\Documents\Power BI Desktop\Custom Connectors)
  4. Download and extract the Power BI connector ZIP
  5. Copy Speckle.pqx to the Custom Connectors directory
  6. Verify Speckle appears under Custom Connectors tab in gateway settings

Configuring Scheduled Refresh (Both Modes)

  1. Open published report in Power BI Service
  2. Select More Options (...) > View semantic model
  3. Access Refresh > Schedule Refresh
  4. Expand "Gateway and cloud connections"
  5. Locate your gateway
  6. Select Data source credentials > Speckle > Edit credentials
  7. Choose OAuth2 authentication method
  8. Sign into Speckle account
  9. Set privacy level per organizational requirements
  10. Configure refresh schedule

Standard Mode: Manual Gateway Connection

If gateway shows "Not configured correctly":

  1. Select "View data sources"
  2. Click "Manually add to gateway"
  3. Create new connection with model URL
  4. Use "Speckle Account" authentication
  5. Map connection to semantic model via dropdown
  6. Select Apply

Gateway Troubleshooting

Error: "Query contains unknown function name: Speckle.GetByUrl" -- The custom connector is not registered. Publish a dataset with standard connectors (CSV, Excel) first, set its data source credentials, then retry the Speckle report. The system requires a verified connector dataset before custom connectors function.

Federation and Multi-Model Analytics

Web App Federation

  1. Create a federated model in the Speckle web app (combines multiple models)
  2. Copy the federated model URL
  3. Load into Power BI using the standard connector workflow

Manual Federation via Helper Function

let
    Model1 = Speckle.GetByUrl("https://app.speckle.systems/projects/.../models/..."),
    Model2 = Speckle.GetByUrl("https://app.speckle.systems/projects/.../models/..."),
    Federated = Speckle.Models.Federate({Model1, Model2})
in
    Federated

Use excludeData = true to federate metadata only (faster loading for large models).

Analytics Use Cases

Use Case Functions Used Description
Material takeoff Speckle.Models.MaterialQuantities Expand material volumes/areas across all elements
Wall layer analysis Speckle.Objects.CompositeStructure Break down composite wall/floor/roof layers
Property extraction Speckle.Objects.Properties Flatten nested properties for filtering and aggregation
Multi-discipline dashboard Speckle.Models.Federate Combine structural, architectural, MEP models
Issue tracking Speckle.Projects.Issues Visualize project issues with 3D context
Cross-model comparison Multiple Speckle.GetByUrl calls Compare versions or design options side by side

Sharing and Publishing

Direct File Sharing

Share .pbix files directly. Recipients can view reports and 3D visualizations but may face limitations editing queries or refreshing data without proper Speckle credentials.

Power BI Service Publishing

Publish to Power BI Service for web access. Other users access reports via the online platform. Scheduled refresh requires Data Gateway configuration (see above).

Permission Requirements

  • Viewing data requires Speckle project access
  • Private projects require authenticated credentials
  • "Permission denied" errors indicate the Speckle account lacks project access

Reference Links

Official Sources