Endpoint Get Table Rows

Name

get_table_rows

Description

Reads a Table of a Dataset.

Related Tables

[Dynamic Table]

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Power BI Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Power BI

Connect to your Power BI account and retrieve data, refresh datasets, etc.

Power BI

[Dynamic Table]

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
DatasetId
WorkspaceId
DaxFilter	true
EnableCustomReplace	1
SearchFor	(?<=(\s*)({|,))(?<TableWithColumn>"[$TableName$]\[(?<Column>.+?)\]")(?=:)--regex
ReplaceWith	$4

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get table rows:

API Destination - Power BI

Connect to your Power BI account and retrieve data, refresh datasets, etc.

Power BI

[Dynamic Table]

Select

Required Parameters
TableName	Fill-in the parameter...
Optional Parameters
DatasetId
WorkspaceId
DaxFilter	true
EnableCustomReplace	1
SearchFor	(?<=(\s*)({|,))(?<TableWithColumn>"[$TableName$]\[(?<Column>.+?)\]")(?=:)--regex
ReplaceWith	$4

ODBC application

Use these SQL queries in your ODBC application data source:

Tables - Get Table Rows for a specified Workspace and Dataset

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

Tables - Get Table rows

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

Tables - Get Table rows in a specified Dataset

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

Tables - Get Table Rows (Use default Workspace and Dataset)

SELECT * 
FROM MyTable

Tables - Insert / Update / Delete Rows for a specified Workspace and Dataset

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"}]'
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Tables - Get Table Rows for a specified Workspace and Dataset

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

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Tables - Get Table rows

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM get_table_rows 
WITH (TableName=''MyTable'')';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Tables - Get Table rows in a specified Dataset

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM get_table_rows 
WITH (TableName=''MyTable'', DatasetId=''aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee'')';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Tables - Get Table Rows (Use default Workspace and Dataset)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * 
FROM MyTable';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Tables - Insert / Update / Delete Rows for a specified Workspace and Dataset

DECLARE @MyQuery NVARCHAR(MAX) = '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"}]''
)';

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];