Speckle Impl Powerbi

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.

Two Components

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

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

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.

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.

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.

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.

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.

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.

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.

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

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

• references/methods.md -- Complete helper function signatures and parameters
• references/examples.md -- Working Power Query examples for common analytics workflows
• references/anti-patterns.md -- Common mistakes and how to avoid them

Official Sources

• https://docs.speckle.systems/connectors/power-bi/power-bi.md
• https://docs.speckle.systems/connectors/power-bi/gateway.md
• https://docs.speckle.systems/connectors/manual-installation/powerbi.md