OrcaScan Power BI Connector

Bring OrcaScan data into Microsoft Power BI using a custom connector (.mez).

---

• Prebuilt .mez location in this repo:
  OrcaScan\bin\AnyCPU\Debug\OrcaScan.mez

Known issues

• If you see The key did not match any rows in the table, double‑check the Sheet ID and selected tables.
• Heavy previews/refreshes may hit API rate limits (~15 req/s). Reduce parallel refreshes or preview reloads.
• Gateway testing in Power BI Online is limited to dataset connection, gateway binding and scheduled refresh validation.

---

Prerequisites

• Power BI Desktop and access to Power BI Online
• Node.js 20.x (install with nvm-windows or the official Node installer)
• Visual Studio Code

Useful links

• https://learn.microsoft.com/en-us/power-query/install-sdk
• https://marketplace.visualstudio.com/items?itemName=PowerQuery.vscode-powerquery-sdk
• https://learn.microsoft.com/en-us/power-query/power-query-sdk-vs-code

---

How to build the .mez file

1. Open Visual Studio Code
2. Go to Extensions
3. Install PowerQuery SDK
4. Open your project folder, e.g. microsoft-power-bi-plugin\OrcaScan (the folder contains the .pq files)
5. Visual Studio Code will tell you it recognizes a Power Studio project in the right bottom corner
6. Press CTRL+SHIFT+P and select "Run build task" > "Build connector using MakePQX"
    This builds an .mez file in "OrcaScan\bin\AnyCPU\Debug" in the project folder

Note: Make sure to open the folder containing the .pq files in step 4, as otherwise Visual Studio Code will result in an error in step 6. Note: the connector file should be OrcaScan\bin\AnyCPU\Debug\OrcaScan.mez.

---

Usage in Power BI Desktop

7. Copy this .mez file to: %USERPROFILE%\Documents\Power BI Desktop\Custom Connectors\
8. Launch Microsoft Power BI Desktop
9. Go to File > Options and settings > Options > Security > Data Extensions
    Select "(Not Recommended) Allow any extension to load without validation or warning" and restart Power BI Desktop
10. After restart select "Get data from other sources"
11. Search for Orca Scan
12. Select the Orca Scan connector and click "Connect"
13. Enter your Orca Scan API Key in the popup and click "Connect"
14. Select the sheets you want and click "Load"
15. The sheets load into Power BI Desktop

---

Usage in Power BI Online for testing

16. Install Power BI Gateway (Standard Mode) -> https://go.microsoft.com/fwlink/?LinkId=2116849&clcid=0x409&culture=en-us&country=us
17. Open "On-premises data gateway" app on the Windows PC
18. Sign in with the same account you use for Power BI Online
19. Go to "Connectors"
20. Set the folder to %USERPROFILE%\Documents\Power BI Desktop\Custom Connectors\
    The gateway will load the custom connector and display it as a Connector
21. Open Power BI Online -> https://app.powerbi.com/
22. Sign in with the same account
23. Go to the cogwheel in the top right and click "Manage connections and gateways"
24. Click "On-premises data gateway" and verify your gateway is listed
25. Enable the gateway by clicking the checkmark in front of it
26. Go to the "Connections" tab and click "New"
27. Select "On-premises" and apply these settings, then click "Create":
    - Gateway cluster name: select your gateway
    - Connection name: OrcaScan
    - Connection type: OrcaScan
    - Authentication method: Key
    - Account key: your OrcaScan API Key
    - Privacy level: Organizational
28. Go back to Power BI Desktop and create a small visual based on your loaded sheet
29. Save as a .pbix file
30. Publish via Home > Publish and select a workspace
31. After publish, a popup says "Your file was published, but disconnected". Click "Open dataset settings"
    This opens Power BI Online
32. Open "Gateway and cloud connections" and in the Extension dropdown select the gateway created above
33. Open "Refresh" and enable Daily refresh
34. Set a time, for example 06:00 UTC

Note: These refreshes may generate errors or pass. If they pass, the .mez functions in Power BI Online for your scenario. For now this is the extent of what can be tested online.

---

Project structure & assets

OrcaScan/
├─ OrcaScan.pq
├─ OrcaScan.query.pq
├─ OrcaScan.proj
├─ resources.resx
├─ OrcaScan16.png
├─ OrcaScan20.png
├─ OrcaScan24.png
├─ OrcaScan32.png
├─ OrcaScan40.png
├─ OrcaScan48.png
├─ OrcaScan64.png
├─ OrcaScan80.png
└─ bin/AnyCPU/Debug/OrcaScan.mez

Logo/Icons
The connector uses the PNG icons listed above. Update these files to change the logo shown in Power BI.

---

Notes and limits

• The connector reads schema from /fields and rows from /rows.
• Approximate API rate limit 15 requests per second. Avoid unnecessary preview refreshes.
• Empty lists for photo and attachment are converted to null to prevent type errors.
• Date and time handling preserves timezone if the API returns ISO 8601 with Z or an offset. Otherwise standard date or datetime is used.

---