Power BI Connector

ZappySys Power BI Connector provide read / write capability inside your app (see list below), using these drag and drop , high performance connector you can perform many Power BI operations without any coding. You can use this connector to integrate Power BI data inside apps like SSIS, SQL Server or popular ETL Platforms / BI Tools/ Reporting Apps / Programming languages (i.e. Informatica, Power BI, SSRS, Excel, C#, JAVA, Python)

Download for SSIS Download for Other Apps Documentation

Video Tutorial - Power BI - Connector REST API Integration

Click on your App below to get started with Power BI Integration

Choose your App / Scenario for which you like to integrate Power BI

Actions supported by Power BI Connector

Power BI Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.
 Get Workspaces
Get all user's Workspaces.    [Read more...]
 Get a Workspace
Get a Workspace by ID.    [Read more...]
Parameter Description
Id
 Get Datasets
Get Datasets from default or specified Workspace. If WorkspaceId parameter is not supplied, default Workspace will be used.    [Read more...]
Parameter Description
WorkspaceId
 Get a Dataset
Get a Dataset by ID.    [Read more...]
Parameter Description
Id
WorkspaceId
 Create a Push Dataset
Creates a Push Dataset with Tables.    [Read more...]
Parameter Description
Definition JSON describing the created Dataset and its Tables
Option Value
Example { "name": "SalesMarketing", "defaultMode": "Push", "tables": [ { "name": "Product", "columns": [ { "name": "ProductID", "dataType": "Int64" }, { "name": "Name", "dataType": "string" }, { "name": "Category", "dataType": "string" }, { "name": "IsComplete", "dataType": "bool" }, { "name": "ManufacturedOn", "dataType": "DateTime" }, { "name": "Sales", "dataType": "Double", "formatString": "Currency" } ] } ] }
WorkspaceId
Option Value
Default
 Delete a Dataset
Delete a Dataset by ID.    [Read more...]
Parameter Description
Id
WorkspaceId
Option Value
Default
 Refresh a Dataset
Refreshes a Dataset by ID.    [Read more...]
Parameter Description
Id
WorkspaceId
Option Value
Default
 Get Tables
Gets a list of Tables of a Dataset    [Read more...]
Parameter Description
DatasetId
WorkspaceId
Option Value
Default
 Get Table Columns
Gets Columns of a Table    [Read more...]
Parameter Description
TableName
DatasetId
WorkspaceId
Option Value
Default
 Get Table Rows
Reads a Table of a Dataset.    [Read more...]
Parameter Description
TableName
DatasetId
WorkspaceId
Option Value
Default
DaxFilter
Option Value
All Rows true
'Equal to' operator example [Column] = "AnyValue"
'And' operator example [Column1] = "AnyValue1" && [Column2] = "AnyValue2"
'And' and 'Or' operators used together [Column1] = "AnyValue" && ([Column2] = 1234 || [Column3] < 1000)
'IN' operator example [Column] IN { "Value1", "Value2"}
 Insert Rows into Push Dataset Table
Inserts rows into a Push Dataset Table    [Read more...]
Parameter Description
TableName
DatasetId
WorkspaceId
Option Value
Default
 Truncate a Push Dataset Table
Reads a Table of a Dataset.    [Read more...]
Parameter Description
TableName
DatasetId
WorkspaceId
Option Value
Default
 Execute a DAX query
Executes a DAX query on a Dataset in a default or specified Workspace.    [Read more...]
Parameter Description
DAX query Your DAX Query. Examples: EVALUATE FILTER('MyTable',true) --OR-- more complex EVALUATE FILTER(''MyTable'', [SomeNameColumn] = "ZZZ" && ([SomeIdColumn] = 10643 || [SomeIdColumn] = 10952 ) )
DatasetId
WorkspaceId
Option Value
Default
 Generic Request
This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL.    [Read more...]
Parameter Description
Url API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
Body Request Body content goes here
IsMultiPart Check this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value) ==== Raw Upload (Content-Type: application/octet-stream) ===== To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip ) ==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) ===== To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored. If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type. See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored). file1=@c:\data\Myfile1.txt file2=@c:\data\Myfile2.json file2.Content-Type=application/json SomeField1=aaaaaaa SomeField1.Content-Type=text/plain SomeField2=12345 SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab SomeFieldStartingWithAtSign=\@MyTwitterHandle
Filter Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Headers Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair

Power BI Connector Examples (For ODBC PowerPack)

This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

Workspaces - Get Workspaces    [Read more...]

SELECT * 
FROM Workspaces

Workspaces - Get a Workspace    [Read more...]

SELECT * 
FROM Workspaces
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'

Datasets - Get Datasets    [Read more...]

SELECT * 
FROM Datasets

Datasets - Get Datasets in a specified Workspace    [Read more...]

SELECT * 
FROM Datasets
WITH (WorkspaceId = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')

Datasets - Get a Dataset    [Read more...]

SELECT * 
FROM Datasets
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'

Datasets - Create a Push Dataset    [Read more...]

SELECT * 
FROM create_push_dataset
WITH (Definition='{
  "name": "My Push Dataset Name",
  "defaultMode": "Push",
  "tables": [
	{
      "name": "Products",
      "columns": [
        {
          "name": "Id",
          "dataType": "Int64"
        },
        {
          "name": "Name",
          "dataType": "string"
        },
        {
          "name": "Category",
          "dataType": "string"
        },
        {
          "name": "IsComplete",
          "dataType": "bool"
        },
        {
          "name": "ManufacturedOn",
          "dataType": "DateTime"
        },
        {
          "name": "Sales",
          "dataType": "Int64",
          "formatString": "Currency"
        },        
        {
          "name": "Price",
          "dataType": "Double",
          "formatString": "Currency"
        }
      ]
    }
  ]
}'
)

-- More info on creating a Push Dataset:
-- https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-post-dataset

Datasets - Create a Push Dataset with 2 Tables    [Read more...]

SELECT * 
FROM create_push_dataset
WITH (Definition='{
  "name": "My Push Dataset Name",
  "defaultMode": "Push",
  "tables": [
     {
      "name": "Customers",
      "columns": [
        {
          "name": "Id",
          "dataType": "Int64"
        },
        {
          "name": "Name",
          "dataType": "string"
        }
      ]
    },
	{
      "name": "Products",
      "columns": [
        {
          "name": "Id",
          "dataType": "Int64"
        },
        {
          "name": "Name",
          "dataType": "string"
        },
        {
          "name": "Category",
          "dataType": "string"
        },
        {
          "name": "IsComplete",
          "dataType": "bool"
        },
        {
          "name": "ManufacturedOn",
          "dataType": "DateTime"
        },
        {
          "name": "Sales",
          "dataType": "Int64",
          "formatString": "Currency"
        },        
        {
          "name": "Price",
          "dataType": "Double",
          "formatString": "Currency"
        }
      ]
    }
  ]
}'
)

-- More info on creating a Push Dataset:
-- https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-post-dataset

Datasets - Delete a Dataset    [Read more...]

SELECT *
FROM delete_dataset
WHERE Id = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'

-- More info on deleting a Dataset:
-- https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/delete-dataset

Datasets - Refresh a Dataset    [Read more...]

SELECT *
FROM refresh_dataset
WHERE Id = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'
                
-- More info on refreshing a Dataset:
-- https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset

Tables - Get Tables    [Read more...]

SELECT * 
FROM get_tables

Tables - Get Tables in a specified Dataset    [Read more...]

SELECT * 
FROM get_tables
WITH (DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')

Tables - Get Table Columns    [Read more...]

SELECT * 
FROM get_table_columns
WITH (TableName='MyTable')

Tables - Get Table Columns in a specified Dataset    [Read more...]

SELECT * 
FROM get_table_columns
WITH (TableName='MyTable',
      DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')

Tables - Get Table Rows (Use default Workspace and Dataset)    [Read more...]

SELECT * 
FROM MyTable

Tables - Get Table Rows for a specified Workspace and Dataset    [Read more...]

SELECT *
FROM get_table_rows
WITH(
	  "TableName"='Products'
	, "DatasetId"='11b6c287-51d3-4061-bed8-811a4e5f6ce9'
	, "WorkspaceId"='848353e2-f3b1-4fb4-89d7-44e84b8bdf9f'
)

Tables - Insert / Update / Delete Rows for a specified Workspace and Dataset    [Read more...]

INSERT INTO Products 
SOURCE(
  'MSSQL',
  'Data Source=localhost\developer;Initial Catalog=Northwind;Integrated Security=true',
  '  SELECT T.* FROM (       SELECT  TOP 50                
      ProductName AS [Name]              
      ,C.CategoryName AS Category              
      ,Discontinued AS IsComplete              
      ,GETDATE() AS ManufacturedOn              
      ,CAST(UnitPrice * ReOrderLevel * 100 AS BIGINT) AS Sales              
      ,CAST(UnitPrice AS DECIMAL) AS Price       
      FROM Northwind.dbo.Products AS P       
      JOIN Northwind.dbo.Categories C ON P.CategoryId = P.CategoryId       
      ) AS T              
      CROSS JOIN GENERATE_SERIES(1, 2000)             
      -- COMMENT:  50 x 2000 = 100 000 rows  
   '
) 
CONNECTION(
  Parameters = '[{ Name: "TokenUrl",Value:"https://login.microsoftonline.com/organizations/oauth2/v2.0/token"}                         
  ,{ Name: "DatasetId",Value: "6a0e04da-a6e4-4533-abe4-30fcabd0e2a5"},                      
  { Name: "WorkspaceId",Value: "848353e2-f3b1-4fb4-89d7-44e84b8bdf9f"}]'
)

Tables - Using an INSERT statement    [Read more...]

INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, 'Glass', true, '2001-02-03', 195.95)

Tables - Using an INSERT statement in a specified Dataset    [Read more...]

INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, 'Glass', true, '2001-02-03', 195.95)
WITH (DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')

Tables - Get Table rows    [Read more...]

SELECT * 
FROM get_table_rows 
WITH (TableName='MyTable')

Tables - Get Table rows in a specified Dataset    [Read more...]

SELECT * 
FROM get_table_rows 
WITH (TableName='MyTable', DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')

Tables - Truncate a Push Dataset Table    [Read more...]

SELECT *
FROM truncate_push_dataset_table
WITH (TableName='MyTable')
                
-- More info on truncating a Push Dataset Table:
-- https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-delete-rows

Tables - Truncate a Push Dataset Table in a specified Dataset    [Read more...]

SELECT *
FROM truncate_push_dataset_table
WITH (DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee', TableName='MyTable')
                
-- More info on truncating a Push Dataset Table:
-- https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-delete-rows

Execute a DAX query - Evaluating a Table    [Read more...]

SELECT * 
FROM execute_dax_query
WITH (Query='EVALUATE ''MyTable''')

-- More info on 'EVALUATE' statement and DAX queries:
-- https://dax.guide/st/evaluate/
-- https://learn.microsoft.com/en-us/dax/dax-queries
-- https://learn.microsoft.com/en-us/dax/filter-functions-dax
-- https://learn.microsoft.com/en-us/dax/dax-syntax-reference

Execute a DAX query - Using FILTER function with simple expression    [Read more...]

SELECT * 
FROM execute_dax_query 
WITH (Query='EVALUATE FILTER(''MyTable'', [MyColumn] = "MyValue"')

-- More info on 'EVALUATE' statement and DAX queries:
-- https://dax.guide/st/evaluate/
-- https://dax.guide/filter/
-- https://dax.guide/operators/
-- https://learn.microsoft.com/en-us/dax/dax-queries
-- https://learn.microsoft.com/en-us/dax/dax-syntax-reference

Execute a DAX query - Using FILTER function with AND and OR operators    [Read more...]

SELECT * 
FROM execute_dax_query 
WITH (Query='EVALUATE FILTER(''MyTable'', [MyColumn1] = "MyValue" && ([MyColumn2] > 0 || [MyColumn3] <= 1000))')

-- More info on 'EVALUATE' statement and DAX queries:
-- https://dax.guide/operators/
-- https://learn.microsoft.com/en-us/dax/dax-queries
-- https://learn.microsoft.com/en-us/dax/dax-syntax-reference

Execute a DAX query - Selecting specific columns from a Table    [Read more...]

SELECT * 
FROM execute_dax_query
WITH (Query='EVALUATE 
             SELECTCOLUMNS (
                ''MyTable'',
                "MyColumn1 alias", [MyColumn1],
                "MyColumn2 alias", [MyColumn2]
             )
             ORDER BY "MyColumn2 alias"'
    )
    
-- More info on 'EVALUATE' statement and DAX queries:
-- https://dax.guide/st/evaluate/
-- https://dax.guide/selectcolumns/   
-- https://learn.microsoft.com/en-us/dax/dax-queries
-- https://learn.microsoft.com/en-us/dax/dax-syntax-reference

Execute a DAX query - Selecting and sorting TOP N rows    [Read more...]

SELECT * 
FROM execute_dax_query
WITH (Query='EVALUATE 
             TOPN(1000, ''MyTable'', [MyColumnOrExpression], ASC)')

-- More info on 'EVALUATE' statement and DAX queries:
-- https://dax.guide/st/evaluate/
-- https://dax.guide/topn/
-- https://learn.microsoft.com/en-us/dax/dax-queries
-- https://learn.microsoft.com/en-us/dax/dax-syntax-reference

Execute a DAX query - A complicated query    [Read more...]

SELECT * 
FROM execute_dax_query
WITH (Query='
DEFINE
    VAR MinimumAmount = 2000000
    VAR MaximumAmount = 8000000
EVALUATE
FILTER (
    ADDCOLUMNS (
        SUMMARIZE (Sales, Products[Category]),
        "CategoryAmount", [Sales Amount]
    ),
    AND (
        [CategoryAmount] <= MinimumAmount,
        [CategoryAmount] >= MaximumAmount
    )
)
ORDER BY [CategoryAmount]"')

-- More info on 'EVALUATE' statement and DAX queries:
-- https://dax.guide/st/evaluate/
-- https://dax.guide/addcolumns/
-- https://dax.guide/summarize/
-- https://dax.guide/st/order-by/
-- https://learn.microsoft.com/en-us/dax/dax-queries
-- https://learn.microsoft.com/en-us/dax/filter-functions-dax
-- https://learn.microsoft.com/en-us/dax/dax-syntax-reference

Generics - A simple generic API request    [Read more...]

SELECT * 
FROM generic_request
WITH (Url='/groups',
Filter='$.value[*]')


/*
EXPLANATION:
- This configuration calls Power BI REST API "Get Groups" endpoint and gets the Workspaces back.
- This is achieved by "/groups" value in the "Url" parameter.
- The SQL query parameter "Filter" uses JsonPath "$.value[*]".
- This gets JSON objects from "value" array and transforms them into SQL rows.

MORE INFORMATION:
- About "Get Groups" REST API endpoint:
  https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups
- About JsonPath used in "Filter" parameter:
  https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet  
*/

Generics - A generic API request with URL parameter    [Read more...]

SELECT * 
FROM generic_request
WITH (Url='/groups?$filter=contains(name,''MyWorkspace'') or name eq ''My Blue Workspace''',
Filter='$.value[*]')


/*
EXPLANATION:
- This configuration calls Power BI REST API "Get Groups" endpoint and gets the Workspaces back.
- This is achieved by "/groups" value in the "Url" parameter.
- Workspaces are filtered on the Power BI REST API side by using the "$filter" URL parameter.
- Only those Workspaces are returned that:
  > contain a string value "MyWorkspace" or
  > if the Workspace name is "My Blue Workspace" (each single quote is escaped with two single quotes).
- The SQL query parameter "Filter" uses JsonPath "$.value[*]".
- This gets JSON objects from "value" array and transforms them into SQL rows.

MORE INFORMATION:
- About "Get Groups" REST API endpoint:
  https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups
- About JsonPath used in "Filter" parameter:
  https://zappysys.com/blog/jsonpath-examples-expression-cheetsheet  
*/

Download for SSIS Download for Other Apps