Creating a Semantic Model in Power BI and Connecting to It via n8n

This guide focuses on creating a push-enabled semantic model (dataset) in Power BI for programmatic updates from n8n Cloud. Push datasets allow real-time row additions via REST API, which n8n's Power BI node wraps.

While this setup is reliable for Power BI Pro users, streaming/push datasets are in retirement mode—new creations will end on October 31, 2027, per Microsoft announcements, but existing ones will remain functional indefinitely.

The end result: n8n can push data (e.g., Shopify exports) to your model, enabling near-real-time updates.

Step 1: Create the Push Semantic Model in Power BI Service

Use the Power BI Service browser interface (app.powerbi.com)—no Desktop needed for simple push models, though Desktop can publish more complex ones if you add relationships/DAX later.

1. Log In and Navigate:
   • Go to app.powerbi.com, sign in with your Pro account.
   • Left sidebar > Workspaces > Select "Shopify" (or create one: + New > Workspace > Name: "Shopify" > Create. Pro allows unlimited workspaces).
2. Create the Push Dataset:
   • Click + New > Streaming dataset.
   • Select "API" (ignore the retirement warning—creation is still allowed until 2027).
   • Dataset name: e.g., "ShopifyProducts".
   • Values from stream: Define your columns (e.g., for Shopify: "Product ID" as Text, "Variant Price" as Decimal number). Start with what you need (up to 75 max); you can add later via API if under limit.
   • Historic data analysis: Turn ON (enables permanent storage for up to 5M rows; OFF is pure streaming with 1-hour expiry).
   • Click Create—dataset appears under "Datasets + dataflows".
     
3. Add Service Principal:
   • In Shopify workspace go to Manage Access > Add people or group.
   • Copy in https://portal.azure.com/  > Manage Microsoft Entra ID > Manage > App registrations > View all applications in the directory > {name of app connected to N8N} > Display name.
   • Add copied display name to Power BI Access Management.
   • Make newly added name a member.
4. Test the Model:
   • Click three dots right to Semantic Model name > Create report > Add fields.
   • Report shows "No data yet"—normal until n8n pushes.
     

Lessons learned: Keep columns minimal at first (e.g., 10-20) to avoid schema rework. If you need >75 columns, split into multiple tables/datasets and relate them in Power BI (composite mode).

Step 2: Get Group ID (Workspace ID) and Dataset ID

These IDs are required for n8n's Power BI node to target your model.

1. Group ID:
   • In Power BI Service > Workspaces > Click "Shopify".
   • Browser URL: https://app.powerbi.com/groups/{GROUP_ID}/...—copy the GUID after /groups/.
     
2. Dataset ID:
   • In Shopify workspace > Click your dataset (e.g., "ShopifyProducts").
   • Browser URL: https://app.powerbi.com/groups/{GROUP_ID}/datasets/{DATASET_ID}/...—copy the GUID after /datasets/ (e.g., "85e45cf9-58ee-45d7-b89b-87fdc3258976").
     

Attention point: IDs are GUIDs—case-sensitive, no spaces. If using "My workspace," Group ID is "me" (not a GUID)—use a custom workspace like "Shopify" for reliability.

Step 3: Set Up Authentication (Microsoft Entra ID App)

n8n needs OAuth2 credentials for API access—no user login required.

1. Create the App in Azure Portal (portal.azure.com, sign in with Pro account):
   • Microsoft Entra ID > App registrations > + New registration.
   • Name: "n8n-PowerBI-App".
   • Supported account types: "Accounts in this organizational directory only".
   • Redirect URI: Leave blank (client credentials flow).
   • Register > Copy "Application (client) ID" and "Directory (tenant) ID".
2. Add Client Secret:
   • Certificates & secrets > + New client secret > Description: "n8n" > Expires: 24 months > Create > Copy the value (shown once).
3. Add Permissions:
   • API permissions > + Add a permission > Power BI Service.
   • Application permissions: Dataset.ReadWrite.All, Workspace.Read.All.
   • Grant admin consent for your tenant > Yes.

Lessons learned: Always re-grant consent after adding permissions—skipping this causes "InvalidToken" 403s. Use client credentials (not delegated) for n8n automation—no personal login needed.

Step 4: Connect via n8n Cloud (Using Power BI Node)

n8n's verified Power BI node (published by Universo Automático, v0.5.71, 6,971+ downloads) supports operations like "Add Rows" for push datasets.

1. Create Credential in n8n:
   • n8n > Credentials > + New > Power BI OAuth2 API.
   • Fill with your Entra app details (client ID, secret).
     
   • Test—should connect - popup will appear (check if allowed or blocked).
2. Build Simple Connection Workflow:
   • New workflow > Add Power BI node.
   • Credential: Your new one.
   • Resource: Dataset.
   • Operation: Get Dataset (simplest connection test).
   • Group (Workspace) Name or ID: Your Group ID (GUID).
   • Dataset Name or ID: Your Dataset ID (GUID).
   • Execute—expected: Dataset metadata (e.g., name, addRowsAPIEnabled: true).
3. Add Rows (Update Data):
   • Change Operation: Add Rows.
   • Table Name or ID: "RealTimeData" (or from Push URL).
   • Data: JSON array matching your schema, e.g.:
     text

     
     

     [
       {
         "Product ID": "TEST123",
         "Title": "Test Product",
         "Variant Price": 99.99
       }
     ]

     
     
   • Execute—rows added, visible in dashboard.

Lessons learned: n8n Cloud's verified nodes work reliably for Pro, but table dropdowns may error if IDs are wrong—use GUIDs, not names, for accuracy. Batch pushes (up to 1,000 rows) to avoid rate limits (~200K rows/hour on Pro). If "Error fetching options," regenerate credential.

Attention Points and Lessons Learned

• Retirement: Streaming/push datasets are retiring for new creations on October 31, 2027.
• Performance: Push is near-real-time but limited to 5M rows with historic ON—monitor via dataset settings.
• Security: Use service principals (client credentials)—no user tokens. EU compliance: All in West Europe regions.
• Costs: €0 extra; Pro is sufficient.
• Success Key: Start small (5 columns, 1 row test)—scale up. If IDs change, update n8n immediately.