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

Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.

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

Read and write Microsoft Power BI data effortlessly. Access, refresh, and manage datasets, reports, and dashboards — almost no coding required.

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:

Read table rows in a workspace and dataset

<p>Gets rows from a table in a specific workspace and dataset. Supply <code>TableName</code>, <code>DatasetId</code>, and <code>WorkspaceId</code> in the <code>WITH</code> clause.</p>

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

Read table rows (using function)

<p>Gets rows from a table using the <code>get_table_rows</code> function. Supply <code>TableName</code> in the <code>WITH</code> clause.</p>

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

Read table rows in a dataset (using function)

<p>Gets rows from a table in a specific dataset using the <code>get_table_rows</code> function. Supply <code>TableName</code> and <code>DatasetId</code> in the <code>WITH</code> clause.</p>

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

Read table rows

<p>Gets rows from a table in the default dataset. Supply the table name in the <code>FROM</code> clause.</p>

SELECT * 
FROM MyTable

Bulk create rows using SQL Server data

<p>Bulk inserts rows into a push dataset table using data from SQL Server. Supply <code>DatasetId</code> and <code>WorkspaceId</code> in the connection string.</p>

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:

Read table rows in a workspace and dataset

<p>Gets rows from a table in a specific workspace and dataset. Supply <code>TableName</code>, <code>DatasetId</code>, and <code>WorkspaceId</code> in the <code>WITH</code> clause.</p>

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];

Read table rows (using function)

<p>Gets rows from a table using the <code>get_table_rows</code> function. Supply <code>TableName</code> in the <code>WITH</code> clause.</p>

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

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Read table rows in a dataset (using function)

<p>Gets rows from a table in a specific dataset using the <code>get_table_rows</code> function. Supply <code>TableName</code> and <code>DatasetId</code> in the <code>WITH</code> clause.</p>

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];

Read table rows

<p>Gets rows from a table in the default dataset. Supply the table name in the <code>FROM</code> clause.</p>

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

EXEC (@MyQuery) AT [LS_TO_POWER_BI_IN_GATEWAY];

Bulk create rows using SQL Server data

<p>Bulk inserts rows into a push dataset table using data from SQL Server. Supply <code>DatasetId</code> and <code>WorkspaceId</code> in the connection string.</p>

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];