> ## Documentation Index
> Fetch the complete documentation index at: https://docs.speckle.systems/llms.txt
> Use this file to discover all available pages before exploring further.

# How to use Speckle for Power BI

> Step-by-step guide for using the Power BI connector and 3D visual

Speckle's connection to Power BI consists of two parts.

* The **Power BI connector** lets you easily load your model from Speckle into Power BI in a tabular format.
* The **3D Viewer Visual** allows you to view and color your models in 3d.

## Setup

<Steps>
  <Step title="Install the connector">
    <Frame>
      <iframe width="100%" height="375" src="https://www.youtube.com/embed/oy_rRzDSB0w?si=LtOoE2wJeb0vy8Fo" title="Speckle Connector Installation" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen />
    </Frame>

    Install your [Power BI connector](/connectors/installation)
  </Step>

  <Step title="Open the Power BI connector">
    1. Select **Get Data**. 2. In the search box, enter *Speckle*. 3. Select
       **Connect to Speckle** and click **Connect**.
  </Step>
</Steps>

<AccordionGroup>
  <Accordion title="Why don't I see Speckle as a data source in Power BI?">
    You need to enable third-party data sources. 1. Go to **File** > **Options
    and settings** > **Options** > **Security**. 2. Under **Data Extensions**,
    select **Allow any extension to load without validation or warning**.

    <div style={{ position: 'relative', paddingBottom: '56.25%', height: 0, overflow: 'hidden' }}>
      <iframe
        src="https://player.vimeo.com/video/1090885358"
        allowFullScreen
        loading="lazy"
        style={{
        position: 'absolute',
        top: 0,
        left: 0,
        width: '100%',
        height: '100%',
        }}
      />
    </div>
  </Accordion>

  <Accordion title="How can I install Power BI connector without changing security settings?">
    If your organization restricts Power BI security setting changes, your IT department can add the Speckle connector's thumbprint to the Windows registry instead.

    **Thumbprint:** `CDC489B709A709E3283568A9B75D75180B1355BE`

    For detailed instructions, see [Microsoft's Docs](https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-trusted-third-party-connectors?source=recommendations#using-the-registry-to-trust-third-party-connectors).
  </Accordion>
</AccordionGroup>

## Loading a Model

<Steps>
  <Step title="Load from Speckle">
    1. Copy Model URL from the web app.
    2. Paste it in the text field and select **OK**.
  </Step>

  <Step title="Import 3D Visual">
    1. In the Visualizations pane, select the three dots **(...),** then select **Import a visual from a file**.
    2. Browse to `Documents/Power BI Desktop/Custom Visuals`.
    3. Select `Speckle 3D Visual.pbiviz` and **Open**.
  </Step>

  <Step title="Visualize a model">
    1. In the **Visualizations** pane, select **Speckle 3D Visual**
    2. Configure the visual:
       * Drag *Model Info* column into the **Model Info** input -> required for **viewing**
       * Drag *Object IDs* column into the **Object IDs** input ->  required for **interactivity**
       * Drag any column into **Tooltip** input -> for **tooltip**
       * Drag any column into **Color by** input -> for **coloring** model
    3. Wait for your model to load in the viewer
  </Step>
</Steps>

<AccordionGroup>
  <Accordion title="Where do I find the 3D visual?">
    You'll find Speckle's 3D visual in the **Documents/Power BI Desktop/Custom Visuals** folder.
  </Accordion>

  <Accordion title="How do I update the 3D visual to a new version?">
    1. From the Visualizations pane, select the More options (...) menu.
    2. Go to Documents/Power BI Desktop/Custom Visuals.
    3. Select the new version of Speckle's 3D visual.
    4. Select Open.
    5. When prompted with the update warning, select Update.
    6. In your report, select instances of Speckle's 3D visual.
    7. From the Visualizations pane, select Table visual to replace the 3D visual temporarily.
    8. Select Speckle's 3D visual from the Visualizations pane again to apply the new version.
  </Accordion>

  <Accordion title="How do I control the visibility of unselected elements in the 3D visual?">
    Use the ghost icon in the 3D visual. By default, unselected elements appear ghosted. Clear the ghost icon to completely hide unselected elements.
  </Accordion>

  <Accordion title="How do I hide the Speckle logo from the 3D visual?">
    If you have a paid plan, you can hide Speckle branding by selecting the arrow in the top bar. Explore plan users can't hide the Speckle logo.
  </Accordion>
</AccordionGroup>

<AccordionGroup>
  <Accordion title="How do I sign in to my Speckle account in Power BI?">
    1. Select **Get Data** from the **Home** ribbon.
    2. Find Speckle under data sources.
    3. Paste your model URL in the text box.
    4. If not already signed in, an authentication dialog will appear.
    5. Click **Sign in** and sign in to your Speckle account from the browser window that opened.
    6. Power BI will store your credentials. So you won't have to log in again next time you load a model.
  </Accordion>

  <Accordion title="Why do I get an 'Access to the resource is forbidden' error?">
    This is a browser-related issue. To fix it:

    1. Go to **File** > **Options and settings** > **Options** > **Security**
    2. Under **Authentication Browser**, clear **Use my default web browser**
  </Accordion>

  <Accordion title="Why do I get a 'Value cannot be null. Parameter name: uriString' error?">
    This authentication error can be resolved by adjusting Power BI's browser settings:

    1. Go to **File** > **Options and settings** > **Options**
    2. Select the **Security** tab
    3. Under **Authentication Browser**, clear **Use my default web browser**
    4. Try loading your model again
  </Accordion>

  <Accordion title="How can I switch to a different account in Power BI?">
    If you are trying to load from a different server, you don't need to do anything. Just load the model from the new server.
    If you want to switch to a different account in the same server, you'll need to clear Power BI's cached authentication data by following these steps:

    1. Close Power BI Desktop completely.
    2. Where do I find my Power BI installation folder?
       * Desktop version (.exe): Navigate to %LocalAppData%\Microsoft\Power BI Desktop (typically C:\Users\[your-username]\AppData\Local\Microsoft\Power BI Desktop)
       * Microsoft Store version: Navigate to C:\Users\[your-username]\Microsoft\Power BI Desktop Store App
    3. Delete following items:
       * CEF folder
       * WebView2 folder
       * Cache folder
       * User.zip file
    4. Restart Power BI Desktop.

    This process will clear all cached data for Power BI Desktop, not just Speckle authentication. You may need to re-authenticate with other data sources as well.
  </Accordion>
</AccordionGroup>

<AccordionGroup>
  <Accordion title="Why do I get a 'Cannot connect to Desktop Service' error?">
    This error occurs for one of two reasons:

    * Desktop service isn't running – See [How do I check if Desktop service is running?](#how-do-i-check-if-desktop-service-is-running) to verify and restart the service.
    * Token cache expired – Follow these steps to clear your cache:
      1. Go to **File** > **Options and settings** > **Data source settings**
      2. Select **[https://app.speckle.systems/](https://app.speckle.systems/)** or your server URL
      3. Select **Clear Permissions**
      4. Go to **%APPDATA%/Speckle/Projects**
      5. Delete **PowerBITokenCache.db** files
      6. Restart Power BI and try again
  </Accordion>

  <Accordion title="How do I check if Desktop service is running?">
    1. Open **Task Manager**.
    2. Look for **Speckle.Desktop.Services**.
    3. If the process appears in the list, it's running.
    4. If the process doesn't appear:
       * Select **Run new task**.
       * Enter this path: **%APPDATA%\Speckle\Desktop Services\Speckle.Desktop.Services.exe**
       * Select **OK**.

    The Speckle.Desktop.Services process should now be running.
  </Accordion>

  <Accordion title="Why do I get an 'Unable to connect remote server' error?">
    The Power BI connector needs Speckle Desktop Service to run on your machine. Make sure Desktop Service is active and running.
  </Accordion>
</AccordionGroup>

<AccordionGroup>
  <Accordion title="Why doesn't my report update after a new version is published?">
    Following reasons might cause this issue:

    1. Refresh preview
       Make sure you clicked **Refresh** in the Power BI ribbon to load the latest version.

    2. Check if you loaded a specific version
       If your model URL contains an "@" symbol, this means you loaded a specific version. Power BI connector will only load that specific version and not any new versions.
  </Accordion>

  <Accordion title="Where's the Model URL in the new Power BI connector?">
    The next-generation Power BI connector doesn't use Model URL input. You can safely ignore it.
  </Accordion>
</AccordionGroup>

<AccordionGroup>
  <Accordion title="Why do I get a 'Permission denied' error?">
    This error usually means you don’t have the required permissions to load the model.

    **Steps to fix it:**

    1. Verify that you have been granted access to the model.
       * If not, contact the project owner and request the necessary permissions.

    2. If you are already added but still see the error, try re-authenticating:
       * In Power BI, go to **Data source settings**.
       * Select **Global permissions**, then double-click on `app.speckle.systems`.
       * Click **Edit**, choose **Speckle Account**, and sign in again.

    After signing in, reload the URL to check if the error is resolved.

    If you're already signed in but can't sign in with your other account, see [How can I switch to a different account in Power BI?](#how-can-i-switch-to-a-different-account-in-power-bi)
  </Accordion>

  <Accordion title="Can I load and visualize private projects with the Power BI connector?">
    Yes. Both the Power BI connector and 3D visual support private projects.
  </Accordion>

  <Accordion title="Why can I view a model in Speckle but can't load it in Power BI?">
    Viewing and loading a model require different permissions. To load a model in a connector, contact the project owner to change your project role.
  </Accordion>
</AccordionGroup>

<AccordionGroup>
  <Accordion title="How do I visualize multiple models in Power BI?">
    Here's how you can load and visualize multiple models with Power BI connector:

    1. First, federate your models in the web app.
    2. Copy the federated model URL
    3. Load federated model in Power BI.

    Visualizing multiple models in Power BI is similar to visualizing a single model.
  </Accordion>
</AccordionGroup>

## Sharing Reports

You can share your Power BI reports with Speckle models in two ways:

* **Share the Power BI file directly**: You can share the .pbix file with colleagues who will be able to open and view the report, including the 3D model visualization. However, they may encounter limitations when attempting to edit queries or refresh data, as these operations require access permissions and the Speckle Desktop service.

* **Publish to Power BI Service**: You can publish your report to Power BI web, making it accessible to others through the online platform where they can view the published report and interact with the 3D visual. If a data gateway has been configured, the Speckle connector and 3D visual support scheduled refresh, ensuring your reports reflect up-to-date data.

## Data Gateway Setup

For production reports that need scheduled refresh, you'll need to set up a data gateway. This allows your Power BI reports to automatically update with the latest Speckle data without manual intervention.

[**Set up Data Gateway →**](/connectors/power-bi/gateway)

<Note>
  Data gateways are only needed for scheduled refresh in Power BI Service. For basic connector usage and 3D visualization, you don't need a gateway.
</Note>

## Helper Functions

We’ve added a series of helper functions to the `Speckle` namespace to make working with Speckle data in Power BI more efficient and user-friendly.

### `Speckle.Projects.Issues`

Fetch issues from a given project, model, or version. <br />

```powerquery theme={null}
Speckle.Projects.Issues(url as text, getReplies as bool)
```

Returns a table with the following columns:

* `Id`
* `Title`
* `Description`
* `Status`
* `Priority`
* `Assignee`
* `Due Date`
* `Created`
* `Updated`
* `Labels`
* `Replies`
* `URL`

This function accepts a `URL` -> project, model and version urls. <br />
*Optionally*, you can provide `getReplies` to extract replies.

**Usage:**<br />

* **Fetch Issues from a project** -> `Speckle.Projects.Issues("https://app.speckle.systems/projects/PROJECT_ID/")`
* **Fetch Issues from a model** -> `Speckle.Projects.Issues("https://app.speckle.systems/projects/PROJECT_ID/models/MODEL_ID/")`
* **Fetch Issues from a version** -> `Speckle.Projects.Issues("https://app.speckle.systems/projects/PROJECT_ID/models/MODEL_ID@VERSION_ID/")`
* **Fetch issues with replies** -> `Speckle.Projects.Issues("https://app.speckle.systems/projects/PROJECT_ID/", true)`

### `Speckle.Objects.Properties`

Quickly access properties of an object without navigating through the nested structure. <br />

```powerquery theme={null}
Speckle.Objects.Properties(inputRecord as record, optional filterKeys as list)
```

Returns a record containing properties and their values from the given `inputRecord`.
*Optionally*, you can provide `filterKeys` to extract only specific properties.

**Usage:**<br />

* **Extracting all properties** -> `Speckle.Objects.Properties([data])`
* **Extracting specific properties:** -> `Speckle.Objects.Properties([data], {"Name", "Number", "Area"})`

<Expandable title="Example">
  This function works with individual records, and can be used when adding custom columns:

  1. Go to **Add Column** > **Custom Column**
  2. Give your new column a name (e.g., "**Object Properties**")
  3. Call the helper function: `Speckle.Objects.Properties([data])`
  4. A new column will be added with the properties from the record object
</Expandable>

***

### `Speckle.Objects.CompositeStructure`

Extract the composite structure of objects such as Walls, Floors, Roofs or any elements with layered structure.

<Note>Currently only supported for **Revit** and **Archicad** models.</Note>

```powerquery theme={null}
Speckle.Objects.CompositeStructure(inputRecord as record, optional outputAsList as nullable logical)
```

*By default*, returns a record containing the composite structure.\
*Optionally*, set `outputAsList` to `true` to return it as a list --> useful for expanding each layer into its own row.

**Usage:**<br />

* **Extracting composite structure** -> `Speckle.Objects.CompositeStructure([data])`
* **Extracting composite structure as a list** -> `Speckle.Objects.CompositeStructure([data], true)`

<Expandable title="Example">
  This function works with individual records, and can be used when adding custom columns:

  1. Go to **Add Column** > **Custom Column**
  2. Give your new column a name (e.g., "**Composite Structure**")
  3. Call the helper function: `Speckle.Objects.CompositeStructure([data], true)`
  4. A new column will be added with the composite structure from the record object
</Expandable>

### `Speckle.Objects.MaterialQuantities`

Access material quantities of an object.

```powerquery theme={null}
Speckle.Objects.MaterialQuantities(inputRecord as record, optional outputAsList as logical)
```

*Returns* a record containing its material quantities.\
*Optionally*, set `outputAsList` to `true` to return quantities as a list, which is helpful for row expansion.

**Usage:**<br />

* **Extracting material quantities** -> `Speckle.Objects.MaterialQuantities([data])`
* **Extracting material quantities as a list** -> `Speckle.Objects.MaterialQuantities([data], true)`

<Expandable title="Example">
  This function works with individual records, and can be used when adding custom columns:

  1. Go to **Add Column** > **Custom Column**
  2. Give your new column a name (e.g., "**Material Quantities**")
  3. Call the helper function: `Speckle.Objects.MaterialQuantities([data], true)`
  4. A new column will be added with the material quantities from the record object
</Expandable>

### `Speckle.Models.MaterialQuantities`

Takes a table and extracts material quantities for all objects, expanding them into separate columns.

```powerquery theme={null}
Speckle.Models.MaterialQuantities(inputTable as table, optional addPrefix as logical)
```

*Returns* a table with material quantities columns added. <br />
*Optionally*, set `addPrefix` to `true` to create prefixed columns like "MQ.materialName", "MQ.area"

**Examples:**<br />

* **Basic usage:** `Speckle.Models.MaterialQuantities(Source)` - Creates columns like "materialName", "area"
* **With prefixing:** `Speckle.Models.MaterialQuantities(Source, true)` - Creates "MQ.materialName", "MQ.area"

<Expandable title="Example">
  This function works with tables and can be used in Power Query:

  1. Select fx icon in the formula bar
  2. In the formula bar, call the function: `= Speckle.Models.MaterialQuantities(Source)` (Replace `Source` with your actual table name)
  3. A new step will be added and new table will be created with the material quantities expanded into separate columns
</Expandable>

### `Speckle.Models.Federate`

Manually federate loaded models in Power BI for use in 3D visual.

```powerquery theme={null}
Speckle.Models.Federate(tables as list, optional excludeData as logical)
```

Accepts a list of tables (each representing a model loaded from Speckle) and *returns* a federated table for use in 3D visual.\
*Optionally*, use `excludeData = true` to federate only the metadata necessary for 3D visual without pulling in all the data.

**Usage:**<br />

* **Federating multiple models** -> `Speckle.Models.Federate({Model1, Model2, Model3})`
* **Federating with metadata only** -> `Speckle.Models.Federate({Model1, Model2, Model3}, true)`

<Expandable title="Example">
  This function works with multiple tables and can be used in Power Query:

  1. Go to **Home** > **Get Data** > **Blank Query**
  2. In the formula bar, call the function: `= Speckle.Models.Federate({Model1, Model2})`
  3. Replace `Model1`, `Model2` with your actual model table names
  4. A new federated table will be created combining all specified models
</Expandable>

### `Speckle.Utils.ExpandRecord`

This function expands a record column into separate columns for each field. It's especially useful in combination with the `Properties` helper.

```powerquery theme={null}
Speckle.Utils.ExpandRecord(table as table, columnName as text, optional FieldNames as list, optional UseCombinedNames as logical)
```

*Optionally*, use `FieldNames` to limit the expanded fields.\
*Optionally*, use `UseCombinedNames = true` to prefix column names with the record column name (e.g., `Properties.Length`).

**Usage:**<br />

* **Expand properties record column** <br />
  `Speckle.Utils.ExpandRecord(Source, "properties")` -> All properties will be expanded into separate columns
* **Expanding subset of fields** <br />
  `Speckle.Utils.ExpandRecord(Source, "properties", {"Length", "Width"})` -> Only Length and Width fields will have separate columns
* **Expanding record columns with combined names** <br />
  `Speckle.Utils.ExpandRecord(Source, "properties", {"Length", "Width"}, true)` -> Length and Width fields will have separate columns with the record column name as a prefix (e.g., `Properties.Length`, `Properties.Width`)

<Expandable title="Example">
  This function works with tables and can be used in Power Query:

  1. Select fx icon in the formula bar
  2. In the formula bar, call the function: `= Speckle.Utils.ExpandRecord(Source, "properties")` (Replace `Source` with your actual table name)
  3. A new step will be added and new table will be created with the properties **record** expanded into separate columns

  <Note>This function works with any record column, not just properties.</Note>
</Expandable>