Power BI Connector - Source Code
<?xml version="1.0" encoding="utf-8"?>
<ApiConfig Name="Power BI"
Desc="Connect to your Power BI account and retrieve data, refresh datasets, etc."
Slug="powerbi-connector"
Id="760a7b31-1ee4-409d-9995-aca954652b96"
Logo="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJYAAACWCAYAAAA8AXHiAAAAIGNIUk0AAHomAACAhAAA+gAAAIDoAAB1MAAA6mAAADqYAAAXcJy6UTwAAAAGYktHRAD/AP8A/6C9p5MAAAAHdElNRQfnBBwMETUkqtEqAAAS1UlEQVR42u2dz4smRxnHv/XO7O5Mspvd/IRkExJikBwUVkQJIoIgYhKCniTqXT0o6N2LB028CEIu8eJNCOJJ8x+IAYMYRNBDYhKSNYKy7MZs1nFm+vHQXd1V1U/96Oqqertm+mHfed+q6qe6qvqzTz1V1V0tsEq0XP3VJTX4KIBvA/gKgIcBnJUJ1P9xCLE/PZFx+QM4IOBNAL8G8CII78iEh79xPUnbiCS5nEJ576VLoE1/vT4H4KcAPinTafTDIjmBIk8UgQC8AuB7AF4VAJoN8MjXrs9un83sHE6pNANUHwPwIjqoCMoFt1100tNHh5Jbn6j9ePO3RQ0BAeAzAH4O4DECgOM07bOCNU92AHwfwOME5WIHXnArUDZ1CZT1AD5/sqQrcgXAd5pGiFRd2ApWpHTX6FECvkgeIDjrVAKo0PzR5v+0EHTZ13OHygpWrLQX6yMg3Oc5xvzJprPqZYCSh9wP4JFUzbObKqNTKhfAtWHZEd4YJo+u5ZAzXX2SyArWPNFdEhdQngtOE2EaRcUDFaY/UVaw5go5g6cOKCkrWLEyo7sDpgM1FdhtASVlBStSaHJCl7xAoHIwtoKVSrYMlHf06IrOQNYKVqwEXIyl+k85gZKygpVBTjNQUlawEsri/acI4GNlBSuBnBSgKCFZK1iR4oUJ2C5QW4BJlRWs1LJ0/ykzUFJWsFJJ7UCtSzoLk9r9p3VJZ2GyAuWUFayZsiigZsCUmrMVrEiZBNSC/aeg+78iZAUrVgKnG04bUFJWsHLIQv2ndUmnVqkdqHVJZ2GyZKAKwqTKClakxNzBoEVv23/y3QE7U1awImXqjXUuC6U9JVo5UFJWsFLJjO5Of+CV+B01EvpPMffoT5UVrEghn+VgA4MII52YK+963H2pQElZwYoUirQgQkkm5UDBZCSzULvKIKDmLjclkBWsSLFaLN9DDxj7T0Lo/pEJWdC9X8a5ox5JS9g+K1iRQoSWgEmjwwEpYeYl44XbwgiP/xUF1LqksxxpNIebEY+jJbmE8g2hXOQOWue2QjOAWpd0FipN4+9XSOnbhIGINHb9t2n9aLBepnVjZidG53YUa/Wxpsi///gMbtvfQdMQxEZeRsU9ZluU3JZF6SNURxsEXHv7Tdy4+k4jxHgnKG4OSihxovsruzUhhGa9ZDyZcA2nZxwxd/vETujGyokA68afv4wPbx3Lkda+IFwh0BUhcC98e4A5/SQzceif7n7k0eP9S3d9/Nb7N4Sv2xuiScumOT7C/27exOGHN4HjBpuNYdUEQI3d7xIBPl5poKRUD9a1Pz2Dw0PCpTt20DS4srMRPwDoCwAuAnmsVftF2L94B/YvXRwdP8rTNptOhOboCLduvI/rV6/i6NZN7AhpzwZL1dBgxcz5L5vMfiRtplQPluz+mgZPCEG/APA4kA+oUYDddm/QIe4cShk2Ozs4f/edOLO/h3+98Xcc3fwAG9F2kwLoSaIACxWz6JzL3ap6q8i3fvclHB01aBq6Wwg8D22TWXPY1gGgzTDygAxzTWQcZtfR0vtT+crQxhERzu3v466HHgKJXTREfdbahrmGKinMT9kHlQlq50ghVYP1wD17rV8ixJMEfLY2oNTzEDXYu3AeZy9ewnFDA1yNAhBjbYK25bYBlRgmVaoG68bNQ/zyN+8AoM+DaKdGoNRziI3A3h0XcNyIwWIZdR5ZMk4sMNmqlUOq9rHOnd3gq09dPiuAy0NsYh/K0IGh4/OhOB3zHDIPQcDumTNoqJ0pJRAExDgroaw5Ko797DXChLBVDdZOOzzftPWoFyhznUe1VA2AjZylsE3DzwRqXdIZyTQ4FguUlt76Vk0DbDZtWWjTTmQJ26SoMM8AS4SnmgmlbrBOFFDoXbJG8aOEGH/LLNgeMKC7y+xeAagdLK6JKgWqNUlG9gSQ0Jdz5E91Nl6EkOIDKjFtlYOlSM1AGWXSoOpGm9StJ6pH98B5bjUtCZSUusEywKgaqC5Bzk1p0wlimCEIXdKJASolY1WDxWFVK1Dtv4GgHjC5Rmj4VTYrNXtj3URSNVjji8YGFg+UTBVtp6dNaBI6H0pdlTZziZyfIk/6HKkbLN/4uhKgzHkD1cfqrRfG0w2yeqHzW6OodbrBJrb/irUBpegQGeZqXDWH8bI2SymgpFQOliE1AwXAPtM5LqGwJbiiIrvMGKkbLN8zWNUAxeSpVFFaqB6/gNuSpwKV2ojVDdZIKgeKhsd05KhQRnMPtPYy9YFWrtqJpXKwaBSsE6gu2E2h93NYzFM6/fEcTLZiuaqdSeoGSxmGVw2U6WO56tsdphmsEEp8QCUmrWqwSB2TGyl1AdWFqL1NmTsNAM2312bgA3YPKQWUlKrBaoX035UCBbTPF2oqlsGi4E5hL6K/2TLICQALqB0o+/F6itdKzbBO66hQlYBn+eoASh4zdOukfEbZW6YbpgIVPIKMkLrB6oWYnzUBpeRHhrpjuaZHccKSjqN4SaVysE4KUPKH4WPZ1m5omNuyTke4Wiv3XANqB8sCTH1Aqb8dXrdUFeFLOo7iBevHSN1gmRdS/q0OKKDfyY1z3Env8gSTrbV1CsKkStVgjbCqFiiZIu/GUg7l5q5s1fFV19U8iaVqsJyjwv7n8oFqU0nbnE3ePcpedMYd4wMBzZNJ6gZLk3qBMtO80wbmhGkIIT6gElNWOVjEBGsEqvvD7VOkBplBo3NKgiuWpwlTSeVgdVI7UGZ51Y9tBsIFRGHrxEndYLlu9KsKKD4LM1qDKse23EgnWcA6uPo8Rvc9Wudt4A9bdOnoA/z36svNZu9ehR/OT/GNGPnzlwOqi7N1hYL5DqmOM9JdpLmSFKyDq89BB0rchnYv0LPpiw6I3Qu0d/npc4fX/7IPaoaGAhkPdrqAJVBzBNCRLHN5oDDOkzCMDL0wTVzScRUplSQD6+Afz6mN9QCArwP0JICHQBKsdNZKfovd28WZuz5xnwmWXXecFx0foDm4huNb/wQd3ZpQDiQGSm645u+zVJhCl3RcRUotScC69e5PgINj4IwABD4N4GcAnnBaC1c4CIghLDZn1UvjmdgZh8XOHjZnL2Jn7x4c3ngdzf+uMYfntVBtsQl+KjBe0lG7SZvqhPmtFJJkq8gNOqiAx0B4EYQn2L1AFUvDhvsdx8J05V/qdbsPbJ+GD3d6Yvc8zlz8KMTueXtZjO0itdly2X9xEJKlPp2Fcr74UjYPmOtPxreZTB6oPCzHSsI9SBsB0HcBuuIFyKyVdkH8uipWU3X7MHG6BLG7j93bH8TIBBQBitgiW4FysTgRKHJnOVkSOu/iQQBPDcU0a2H8DvR/9JR43Snn3Zy7E2J3H3R0kxmkperyjHQCjCdDxhfaNTJ0FMfaRAGHx0oisAgAHgZwv73IIc74OOx3xqcMAkJ9tl2InT3Q4QcY9lPICdS4thZ2tISesxA6iP3piYyXlNMNFwCcibdOeniadTLDcy2bgBA7ejn6Q/MBpd02wznk6iw8GdtHcuKzTrnMFdJarHF4q9Zpjm7nQamv38oKlJofjeHimts1CtwiUFLSg7Vo6xSqq3i+VAKo7o9wrSajvxcwdJeZyUAlBC4NWK5Gs1iN5Vgnl65hMnIBpURw0w6jFR/PpiBTgVrwPu9TrFP3dzHWyXasCk1uoLo8lDv7NIPpW3AWlp5xC0BJyQSWHue3TmY4fqogjWWzdYO5gOoC6qYgLulgU1x+e9MwugVcrBx3N7iAmmJhzFYoqQuMJzbVbBMD1R8U8kJCjB+qCJxuIE96SslisUpNZObTpfExWYGy5Wcku0aIFqn61b3ENqhZ6mnD/aBjU+lyo0IlPj9QGDtTDDBq1zfbf6KgqGgpPCo0w0uwTly6jC0FlJpGWvHUztG3LfcoO75SrmAyKT4q1MKLtWzmPlW5gZLpYtQ07EhPeLLidFxR9Uw3cPGlpgrm6E4YFfbBFEANeWnFdPlX6mDydOzznss6ldT1HJ8cKJmDZ1SoZjVhBn4UVWC+YQtrhb7wEnSJhScfUK30d79aOCd4Vn3mApUQuMQWK5dDvQ3LJq8ksgPlLJrcoVsMt8icnn3eKeJCsrVakmXTR2hDSg6guhihpBPsT+iYp+VeaBFonSobFZpxJacK5llFMo/rL1pGoAJbVLOgQg+6FR3FyiR5ukJfOHaqIMo6henSSJeGlOxAKToOH4ubwyKc6Ff31mad2jA5dEnrj5hjUgMFDFPq3CmZbYtOwat7HV3hbIe6hHUydRX/ShQAiht9MsCMgHLJloCSssVRoRnOb9loqq66+0YJoABtxt/0obxLOj7/iWzR6SnLMCo0a1BqqiDCOjl1yZlvWqBIU2HdLBdUc4HKYL0KjQrNcH7LRjN0e6i0SaP8QFmb0GKdetYm7/Oef2i4xVGhmT5fN8w6hZ6XrLrpgQrzsbTTRe3zXmiuAUXubli6dXLoGqPCrECRLU8mq1wWKiFwFYwKed201snUJeUCFwIKDqA6s8T2jAlhSmnI8tzdEGUlwnSzWaeRrvLAap9UAijS+a4MKCnpRoXOR0XmTRXktU4BuiWAoiFPOW5gR3+W2Xf/0NBRlAySritkx8cB4dipgoxWceh/zHplBMqSoxZpQCVb6ZS8uneedTL/TtWdc97Rsf1FzA/UYJkZXXYGnk7DPu+ZrZNH1xmO9rsIZYHqZtbVDwOUCOnyfN1dzj6wky2NCofwfGc8h2Uzr0xeoPpzWDZT64HqnPkc+7ynZi1fV+hwivM74xGWbXQ6GgYlynFZgLKUTd4F30YJ75IOl5s90lGEBJLWYmW3TqV0C1kos2w0HKkdErApCCtbAEpKpkXoodRbnyqI0m07IOddoxmAau9vJ6uPpZ1bOLjbIlBSsnWF9VgnLqxcAPPmpxxAOcrBJU3dFCQIpsSwJe0K46yTGV6CZTNGha6y5wZKqtjeROGwbNsASkqeTUG0FrHVIOdUAWbqKucuBVSvo5Rf9ooiQ3dHzuBsKTwqNMNLtWxdgBB0a3IKoLQ8lOJYX22SYJ/3nK5Wps1tS1mnXLpqV2i/NTkpUMzZR2rMks7J3ued1P9KttKWsk4pdVEIKFKKFXiF5ciQTvQ+72opp3Q7ERZmju4Uy9a/G4e50S8DUCM11Wxx2xYJ2BeftwiUlLJPQi/aOlnKK0oA5Xd8SIHs9Ozzbi3ZNqYKTN1Yyybnv0UZoLqgGGuMn+lIuKQDBPpoEyXfk9Czhvu5dd0Qa9v4UwmgurAwSkkdQxmWdNiqJZRCrzwpZZ3m6Bp3qvaL0IqpyAWUERezz7u1ioxuARcr9ea2S7ROfl3y6ZYACoHPGAJR+7yX3pa73ONfZnjLlo28ut1H62fyAiWEUTXDr+LcKz7Sc0pXMyWSsk9CR1mYOboR1snUYxehc1gorlvsvoy9sHLs856ary2OCs1wPl2/dTLCcii2DaC6dUrFZupd3rrPO/qLEXSsGk5k2cKsk1leI60kUDZRvXQGqtOxz/uirFPseVtbQcytyfmBUu4eDdxQ7eTu824t7bThftCxFt1w69SFfRCT7XhGJxFQ1uazLenAIlsESsoWXnmS1u+iGbqu81I/KnTUKxdQXK5KerZF54TAld8fq8hUgU039D+A4bgXAYp4vU51yosDYoBa+KjQrMUUK+HXTeKM245Vw/01NnysjECNel45MrR1earC6dnnfQnWac55SQeiCFA0Oq1VOh9LnX2wVovRLeBibWtUaIZLWadQXeWb6VdyAAXAPfPZRZtdIuEk7/Pu/C8TO1VQyjrZdMkY8mcESomzPjTBdHencJ/3acP97VonW1gpXwGg+i+Fa23Pt+5b25LbJVsCSsrWRoVpJjLn6rpbt30SOv7NFLqWByig325GwDRC5tMTcN6jZa0VTYqeJYVfhJlhIjOJLlcN2RUWAIorD3OXqEi0PjiKzkBWsVGhHahS1okLh9Qr/M0U+hligKJRnFA+XPEI05Z0nKdPKCnB+g+AQwBn5zvjEdYpWHdq1QoBRQNWLEyOd0KPy+yJ5tMP0V7DJLJJkkvbIm+D8B6oXQ4ZoDL/FxoeKmjodijg2CjdyEoRk1+fbftjOItSFo+OeY5BrfWvNqK1Qu2nJUq9dcZaMyZBazV3k7wH4K0ZDaZJGrBAEBDvEujl4f9vCBRcjW26YHR955lVJWdZUwOlpgshLZboLZdpOAVzOjYqvEl+KyCuzm+4VhKBBTQ4JoBeAOg1vsY2C5PasqWUgkB1PwV1VsscF8qlHdecFRyndstrAF4IeIwjWJKA1dAxIHYA4HUA3wLoleH/WIx1irFsGaQYUEpdFIB6yyX0rlBlS+glmNokBOD3AL4J4A0BYEeIYGWXJAHr9sdfAuhYVugPAJ4F6McA/Q2Eg2nWabgQYZYtl5hAIYuFMusiAGw2BkzeUk5ulgMAfwXwIwDPAnhVqn/qhzeStN7/AZYDDNF2IGaWAAAAJXRFWHRkYXRlOmNyZWF0ZQAyMDIzLTA0LTI4VDEyOjE3OjQwKzAwOjAwIehXpgAAACV0RVh0ZGF0ZTptb2RpZnkAMjAyMy0wNC0yOFQxMjoxNzo0MCswMDowMFC17xoAAAAodEVYdGRhdGU6dGltZXN0YW1wADIwMjMtMDQtMjhUMTI6MTc6NTMrMDA6MDD64tTGAAAAAElFTkSuQmCC"
Version="1"
EngineVersion="12">
<VersionHistory>
<Change Date="2024-02-27" Type="New">Initial version</Change>
</VersionHistory>
<ServiceUrls>
<ServiceUrl Name="Microsoft Power BI REST API v1.0" Url="https://api.powerbi.com/v1.0/myorg" />
</ServiceUrls>
<Auths>
<!-- AUTHORIZATION - USER CREDENTIALS -->
<Auth Name="UserCreds" Label="User Credentials" Type="OAuth"
Desc="OAuth App must be created in Microsoft Azure AD. These settings typically found here https://docs.microsoft.com/en-us/graph/auth-register-app-v2."
HelpLink="https://docs.microsoft.com/en-us/graph/auth-register-app-v2"
ConnStr="Provider=Custom;OAuthVersion=OAuth2;GrantType=Default;Scope=[$Scope$];ScopeSeparator={space};"
TestEndPoint="get_workspaces">
<Notes>
<![CDATA[<p>Firstly, login into Azure Portal and there create an OAuth application:
<p />
<ol>
<li>Go to <a target="_blank" href="https://portal.azure.com/#home"><b>Azure Portal</b></a> and login there.</li>
<li>Then go to <a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/Overview"><b>Azure Active Directory</b></a>.</li>
<li>On the left side click menu item <b><a target="_blank" href="https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps">App registrations</a></b></li>
<li>Then proceed with clicking <b>New registration</b>.</li>
<li>Enter a name for your application.</li>
<li>Select the account types to support with the <b>Supported account types</b> option.</li>
<li>In <b>Redirect URI</b>, select <i>Web</i>.</li>
<li>In the textbox enter <b>https://zappysys.com/oauth</b> as the Redirect URI or another valid redirect URL.</li>
<li>Use this same Redirect URI in the <b>Redirect URI (must match App Redirect URL)</b> grid row.</li>
<li>Copy <b>Client ID</b> and paste it into the API Connection Manager configuration grid in the <b>Client ID</b> row.</li>
<li>Click on the <b>Endpoints</b> link and copy the <b>OAuth 2.0 authorization endpoint (v2)</b> URL to the <b>Authorization URL</b> grid row. Usually it looks similar to this:
<ul>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize</li>
</ul>
</li>
<li>Copy the <b>OAuth 2.0 token endpoint (v2)</b> URL to the <b>Token URL</b> grid row. Usually it looks similar to this:
<ul>
<li>https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token</li>
</ul>
</li>
<li>Close "Endpoints" popup and create a </i>Client Secret</i> in the <b>Certificates & secrets</b> tab.</li>
<li>Proceed by clicking <b>New client secret</b> and setting expiration period. Copy the client secret and paste it into configuration grid in <b>Client Secret</b> row.</li>
<li>Now lets setup permissions for the app. Click on <b>API Permissions</b> and on the page click Plus Sign <b>Add Permission</b></li>
<li>Click on <b>Microsoft Graph API</b> and then choose <b>Delegated Permissions</b></li>
<li>on Permission list page search or choose permissions as needed. We need to enable following Permissions from 2 Sections: Microsoft Graph API and Power BI Service.</li>
<li>Make sure you have checked below permissions (If you do not need Write feature then you can skip Write scopes)
<pre>
offline_access
Dataset.ReadWrite.All
</pre>
</li>
<li>Click <b>Generate Token</b> to generate tokens.</li>
<li>That's it!</li>
</ol>
]]>
</Notes>
<Params>
<Param Name="AuthUrl" Label="Authorization URL" Required="True"
Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/authorize;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/authorize" />
<Param Name="TokenUrl" Label="Token URL" Required="True"
Options="For Single Tenant=https://login.microsoftonline.com/{ENTER-TENANT-ID-HERE}/oauth2/v2.0/token;For Multi Tenant=https://login.microsoftonline.com/common/oauth2/v2.0/token" />
<Param Name="ClientId" Label="Client ID" Required="True" />
<Param Name="ClientSecret" Label="Client Secret" Secret="True" />
<Param Name="Scope"
Required="True"
Options="offline_access;https://analysis.windows.net/powerbi/api/Dataset.Read.All;https://analysis.windows.net/powerbi/api/Workspace.Read.All;https://analysis.windows.net/powerbi/api/Workspace.ReadWrite.All"
MultiSelectSeparator="{SPACE}"
MultiSelect="True"
Value="offline_access~https://analysis.windows.net/powerbi/api/Dataset.Read.All"
Desc="Permissions you want to use."
Example="offline_access~https://analysis.windows.net/powerbi/api/Dataset.Read.All"
HelpLink="" />
<Param Name="ReturnUrl" Label="Redirect URI (must match App Redirect URI)"
Options="https://zappysys.com/oauth"
Desc="The ReturnUrl must match the URL on the Authentication page for your Application (in Azure Portal, Azure Active Directory > App registrations > Application)" />
<Param Name="DatasetId"
Required="True"
Label="Default DatasetId (select after generating tokens)"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Label="Default WorkspaceId (select after generating tokens)"
Type="Placeholder"
Desc="Leave value empty to use your Power BI default Workspace"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
MultiSelectLimit="500"
/>
<!--below
not exposed as root level property in HTTP Connection so use as placeholder-->
<Param Name="RetryMode" Value="RetryWhenStatusCodeMatch"
Options="None;RetryAny;RetryWhenStatusCodeMatch;" Hidden="True" />
<Param Name="RetryStatusCodeList" Value="429|503" Hidden="True" />
<Param Name="RetryCountMax" Value="20" Hidden="True" />
<Param Name="RetryWaitTimeMs" Value="1000" />
<Param Name="RetryMultiplyWaitTime" Value="True" />
<Param Name="ExtraAttributesForAuthRequest"
Label="Login options"
Value=""
Options="None=;Force login prompt=prompt=login;Force permission select=prompt=consent"
Desc="Choose this if you want to force login prompt or permission prompt."
HelpLink="https://learn.microsoft.com/en-us/azure/active-directory/develop/v2-oauth2-auth-code-flow" />
</Params>
</Auth>
</Auths>
<!-- ======================================================== -->
<!-- ====================== TEMPLATES ======================= -->
<!-- ======================================================== -->
<Template>
<EndPoint Name="TmpWorkspaceOutputColumns">
<OutputColumns>
<Column Name="id" Type="DT_STR" Label="Id" Length="36" />
<Column Name="name" Type="DT_WSTR" Label="Name" Length="150" />
<Column Name="type" Type="DT_WSTR" Label="Type" Length="50" />
<Column Name="isReadOnly" Type="DT_BOOL" Label="IsReadOnly" />
<Column Name="isOnDedicatedCapacity" Type="DT_BOOL" Label="IsOnDedicatedCapacity" />
</OutputColumns>
</EndPoint>
<EndPoint Name="TmpDatasetOutputColumns">
<OutputColumns>
<Column Name="id" Type="DT_STR" Label="Id" Length="36" />
<Column Name="name" Type="DT_WSTR" Label="Name" Length="150" />
<Column Name="webUrl" Type="DT_WSTR" Label="WebUrl" Length="500" />
<Column Name="addRowsAPIEnabled" Type="DT_BOOL" Label="AddRowsAPIEnabled" />
<Column Name="configuredBy" Type="DT_WSTR" Label="ConfiguredBy" Length="500" />
<Column Name="isRefreshable" Type="DT_BOOL" Label="IsRefreshable" />
<Column Name="isEffectiveIdentityRequired" Type="DT_BOOL"
Label="IsEffectiveIdentityRequired" />
<Column Name="isEffectiveIdentityRolesRequired" Type="DT_BOOL"
Label="IsEffectiveIdentityRolesRequired" />
<Column Name="isOnPremGatewayRequired" Type="DT_BOOL"
Label="IsOnPremGatewayRequired" />
<Column Name="targetStorageMode" Type="DT_WSTR" Label="TargetStorageMode"
Length="50" />
<Column Name="createdDate" Type="DT_DBTIMESTAMP" Label="CreatedDate" />
<Column Name="createReportEmbedURL" Type="DT_WSTR" Label="CreateReportEmbedURL"
Length="1000" />
<Column Name="qnaEmbedURL" Type="DT_WSTR" Label="QnaEmbedURL" Length="1000" />
<Column Name="upstreamDatasets" Type="DT_WSTR" Label="UpstreamDatasets" Length="50" />
<Column Name="users" Type="DT_WSTR" Label="Users" Length="50" />
</OutputColumns>
</EndPoint>
</Template>
<!-- ========================================================= -->
<!-- ======================= ENDPOINTS ======================= -->
<!-- ========================================================= -->
<EndPoints>
<!-- ====== GET WORKSPACES ====== -->
<EndPoint Name="get_workspaces"
Label="Get Workspaces"
Template="TmpWorkspaceOutputColumns"
Url="/groups"
Filter="$.value[*]"
Method="GET"
Desc="Get all user's Workspaces."
CachedTtl="60"
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-groups"
>
</EndPoint>
<!-- ====== GET WORKSPACE ====== -->
<EndPoint Name="get_workspace"
Template="TmpWorkspaceOutputColumns"
Label="Get a Workspace"
Url="/groups/[$Id$]"
Method="GET"
Desc="Get a Workspace by ID."
CachedTtl="60"
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/groups/get-group"
>
<Params>
<Param Name="Id"
IsKey="True"
Required="True"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
MultiSelectLimit="500"
/>
</Params>
</EndPoint>
<!-- ====== GET DATASETS ====== -->
<EndPoint Name="get_datasets"
Template="TmpDatasetOutputColumns"
Label="Get Datasets"
Url="/[$WorkspaceId$]datasets"
Filter="$.value[*]"
Method="GET"
Desc="Get Datasets from default or specified Workspace. If WorkspaceId parameter is not supplied, default Workspace will be used."
CachedTtl="60"
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-datasets"
>
<Params>
<Param Name="WorkspaceId"
Type="Placeholder"
IsKey="True"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
</Params>
</EndPoint>
<!-- ====== GET DATASET ====== -->
<EndPoint Name="get_dataset"
Template="TmpDatasetOutputColumns"
Label="Get a Dataset"
Url="/datasets/[$Id$]"
Method="GET"
Desc="Get a Dataset by ID."
CachedTtl="60"
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/get-dataset"
>
<Params>
<Param Name="Id"
IsKey="True"
Required="True"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
</Params>
</EndPoint>
<!-- ====== CREATE PUSH DATASET ====== -->
<EndPoint Name="create_push_dataset"
Label="Create a Push Dataset"
Url="/[$WorkspaceId$]datasets"
Method="POST"
ContentType="application/json"
OutputHeaders="StatusCode"
Desc="Creates a Push Dataset with Tables."
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-post-dataset"
>
<Body><![CDATA[[$Definition$]]]></Body>
<Params>
<Param Name="Definition"
Required="True"
Type="Placeholder"
Desc="JSON describing the created Dataset and its Tables"
Options="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"
}
]
}
]
}"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
</Params>
<OutputColumns>
<Column Label="Id" Name="id" DataType="DT_STR" Length="36" />
<Column Label="Name" Name="name" DataType="DT_WSTR" Length="150" />
<Column Label="Status" Name="name" DataType="DT_WSTR" Length="30" ValueTemplate="Created" />
<Column Name="ResponseHeaders_StatusCode" Label="HttpStatusCode" DataType="DT_I4" />
</OutputColumns>
</EndPoint>
<!-- ====== DELETE DATASET ====== -->
<EndPoint Name="delete_dataset"
Label="Delete a Dataset"
Url="/datasets/[$Id$]"
Method="DELETE"
OutputHeaders="StatusCode"
Desc="Delete a Dataset by ID."
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/delete-dataset"
>
<Params>
<Param Name="Id"
IsKey="True"
Required="True"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Name="ContineOn404Error" Type="Property" Value="True" />
<Param Name="RawOutputDataRowTemplate" Type="Property"
Value="{Id:'[$Id$]', Status:'Deleted'}" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
</Params>
<InputColumns>
<Column Name="Id" Key="True" MapToParam="True" DataType="DT_STR" Length="36" />
</InputColumns>
<OutputColumns>
<Column Name="Id" DataType="DT_STR" Length="36" />
<Column Name="Status" DataType="DT_WSTR" Length="30" />
<Column Name="ResponseHeaders_StatusCode" Label="HttpStatusCode" DataType="DT_I4" />
</OutputColumns>
</EndPoint>
<!-- ====== REFRESH DATASET ====== -->
<EndPoint Name="refresh_dataset"
Label="Refresh a Dataset"
Url="/datasets/[$Id$]/refreshes"
Method="POST"
OutputHeaders="StatusCode"
Desc="Refreshes a Dataset by ID."
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset"
>
<Params>
<Param Name="Id"
Required="True"
IsKey="True"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Name="ContineOn404Error" Type="Property" Value="True" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{}"
ValueTemplate="{Id:'[$Id$]', Status:'RefreshScheduled'}" />
</Params>
<OutputColumns>
<Column Name="Id" DataType="DT_STR" Length="36" />
<Column Name="Status" DataType="DT_WSTR" Length="30" />
<Column Name="ResponseHeaders_StatusCode" Label="HttpStatusCode" DataType="DT_I4" />
</OutputColumns>
</EndPoint>
<!-- ====== GET TABLES ====== -->
<EndPoint Name="get_tables"
Label="Get Tables"
Url="/datasets/[$DatasetId$]/executeQueries"
Method="POST"
ContentType="application/json"
Filter="$.results[*].tables[*].rows[*]"
Desc="Gets a list of Tables of a Dataset"
CachedTtl="60"
>
<Body>
<![CDATA[
{
"queries": [ { "query": "EVALUATE DISTINCT(SELECTCOLUMNS(COLUMNSTATISTICS(), [Table Name]))" } ],
"serializerSettings": { "includeNulls": true }
}
]]>
</Body>
<Params>
<Param Name="DatasetId"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Hidden="True" Name="EnableCustomReplace" Type="Property" Value="1" />
<!--Actual Regex: (?<=(\s*)({|,))(?<QuotedColumn>"\[(?<Column>.+?)\]")(?=:) -->
<Param Hidden="True" Name="SearchFor" Type="Property"
Value="(?<=(\s*)({|,))(?<QuotedColumn>"\[(?<Column>.+?)\]")(?=:)--regex" />
<Param Hidden="True" Name="ReplaceWith" Type="Property" Value=""$4"" />
</Params>
<OutputColumns>
<Column Label="Name" Name="Table Name" DataType="DT_WSTR" Length="250" />
</OutputColumns>
</EndPoint>
<!-- ====== GET TABLE COLUMNS ====== -->
<EndPoint Name="get_table_columns"
Label="Get Table Columns"
Url="/datasets/[$DatasetId$]/executeQueries"
Method="POST"
ContentType="application/json"
Filter="$.results[*].tables[*].rows[*]"
Desc="Gets Columns of a Table"
CachedTtl="60"
>
<Params>
<Param Name="TableName"
Required="True"
Type="Placeholder"
Functions="JSONENC"
OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="Name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$];DatasetId=[$DatasetId$]"
MultiSelectLimit="500"
/>
<Param Name="DatasetId"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Hidden="True" Name="EnableCustomReplace" Type="Property" Value="1" />
<!--Actual Regex: (?<=(\s*)({|,))(?<QuotedColumn>"\[(?<Column>.+?)\]")(?=:) -->
<Param Hidden="True" Name="SearchFor" Type="Property"
Value="(?<=(\s*)({|,))(?<QuotedColumn>"\[(?<Column>.+?)\]")(?=:)--regex" />
<Param Hidden="True" Name="ReplaceWith" Type="Property" Value=""$4"" />
</Params>
<OutputColumns>
<Column Label="Name" Name="Column Name" DataType="DT_WSTR" Length="200" />
<Column Label="MinValue" Name="Min" DataType="DT_WSTR" Length="2000" />
<Column Label="MaxValue" Name="Max" DataType="DT_WSTR" Length="2000" />
<Column Name="Cardinality" DataType="DT_I4" />
<Column Label="MaxLength" Name="Max Length" DataType="DT_I4" />
</OutputColumns>
<Body>
{
"queries": [ { "query": "EVALUATE FILTER(COLUMNSTATISTICS(), [Table Name]=\"[$TableName$]\")" } ],
"serializerSettings": { "includeNulls": true }
}
</Body>
</EndPoint>
<!-- ====== GET TABLE ROWS ====== -->
<EndPoint Name="get_table_rows"
Label="Get Table Rows"
Url="/datasets/[$DatasetId$]/executeQueries"
Method="POST"
ContentType="application/json"
Filter="$.results[*].tables[*].rows[*]"
Desc="Reads a Table of a Dataset."
CachedTtl="60"
>
<Params>
<Param Name="TableName"
Required="True"
Type="Placeholder"
Functions="JSONENC"
OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="Name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$];DatasetId=[$DatasetId$]"
MultiSelectLimit="500"
/>
<Param Name="DatasetId"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Name="DaxFilter" Value="true" Functions="JSONENC"
Options="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"}"
HelpLink="https://dax.guide/filter/"
/>
<Param Hidden="True" Name="EnableCustomReplace" Type="Property" Value="1" />
<!--ActualRegex: (?<=(\s*)({|,))(?<TableWithColumn>"TABLE_NAME_GOES_HERE\[(?<Column>.+?)\]")(?=:)-->
<Param Hidden="True" Name="SearchFor" Type="Property"
Value="(?<=(\s*)({|,))(?<TableWithColumn>"[$TableName$]\[(?<Column>.+?)\]")(?=:)--regex" />
<Param Hidden="True" Name="ReplaceWith" Type="Property" Value="$4" />
</Params>
<Body>
{
"queries": [ { "query": "EVALUATE FILTER('[$TableName$]',[$DaxFilter$])" } ],
"serializerSettings": { "includeNulls": true }
}
</Body>
<!-- DYNAMIC CUSTOM FIELDS -->
<OutputColumns>
<Column Name="[$parent.Column Name$]" DataType="DT_WSTR" Length="4000"
Expand="True"
DataEndPoint="get_table_columns"
DataEndPointParameters="TableName=[$TableName$];DatasetId=[$DatasetId$];WorkspaceId=[$WorkspaceId$]"
/>
</OutputColumns>
</EndPoint>
<!-- ====== IN PROGRESS: INSERT INTO PUSH DATASET TABLE ====== -->
<EndPoint Name="insert_push_dataset_table_rows"
Label="Insert Rows into Push Dataset Table"
Url="/datasets/[$DatasetId$]/tables/[$TableName$]/rows"
Method="POST"
ContentType="application/json"
BatchSize="1000"
Desc="Inserts rows into a Push Dataset Table"
>
<Params>
<Param Name="TableName"
Required="True"
Type="Placeholder"
Functions="JSONENC"
OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="Name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$];DatasetId=[$DatasetId$]"
MultiSelectLimit="500"
/>
<Param Name="DatasetId"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Hidden="True" Name="ContineOn404Error" Type="Property" Value="True" />
<Param Hidden="True" Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
<Param Hidden="True" Name="RawOutputDataRowTemplate" Type="Property" Value="{}"
ValueTemplate="{ Status:'Done'}" />
</Params>
<Body><![CDATA[{ "rows": {$rows$} }]]></Body>
<InputColumns>
<Column Name="-Dynamic-"
Expand="True"
DataEndPoint="get_table_columns"
DataEndPointParameters="$WorkspaceId$=[$WorkspaceId$];DatasetId=[$DatasetId$];TableName=[$TableName$]"
Type="DT_WSTR"
Length="4000" />
</InputColumns>
<OutputColumns>
<Column Name="Status" DataType="DT_WSTR" Length="30" />
</OutputColumns>
</EndPoint>
<!-- ====== TRUNCATE PUST DATASET TABLE ====== -->
<EndPoint Name="truncate_push_dataset_table"
Label="Truncate a Push Dataset Table"
Url="/datasets/[$DatasetId$]/tables/[$TableName$]/rows"
Method="DELETE"
ContentType="application/json"
OutputHeaders="StatusCode"
Desc="Reads a Table of a Dataset."
HelpLink="https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-delete-rows"
>
<Params>
<Param Name="TableName"
Required="True"
Type="Placeholder"
Functions="JSONENC"
OptionsEndPoint="get_tables"
OptionsEndPointValueColumn="Name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$];DatasetId=[$DatasetId$]"
MultiSelectLimit="500"
/>
<Param Name="DatasetId"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
<Param Name="ContineOn404Error" Type="Property" Value="True" />
<Param Name="EnableRawOutputModeSingleRow" Type="Property" Value="True" />
<Param Name="RawOutputDataRowTemplate" Type="Property" Value="{}"
ValueTemplate="{DatasetId:'[$DatasetId$]', TableName:'[$TableName$]', Status:'Truncated'}" />
</Params>
<OutputColumns>
<Column Name="DatasetId" DataType="DT_STR" Length="36" />
<Column Name="TableName" DataType="DT_WSTR" Length="150" />
<Column Name="Status" DataType="DT_WSTR" Length="30" />
<Column Name="ResponseHeaders_StatusCode" Label="HttpStatusCode" DataType="DT_I4" />
</OutputColumns>
</EndPoint>
<!-- ====== EXECUTE DAX QUERY ====== -->
<EndPoint Name="execute_dax_query"
Label="Execute a DAX query"
Url="/[$WorkspaceId$]datasets/[$DatasetId$]/executeQueries"
Method="POST"
ContentType="application/json"
Filter="$.results[*].tables[*].rows[*]"
Desc="Executes a DAX query on a Dataset in a default or specified Workspace."
CachedTtl="60"
HelpLink="https://dax.guide/st/evaluate">
<Params>
<Param Name="Query"
Label="DAX query"
Required="True"
Type="Placeholder"
Functions="JSONENC"
Editor="MultiLine"
Desc="Your DAX Query. Examples: EVALUATE FILTER('MyTable',true) --OR-- more complex EVALUATE FILTER(''MyTable'', [SomeNameColumn] = "ZZZ" && ([SomeIdColumn] = 10643 || [SomeIdColumn] = 10952 ) )"
/>
<Param Name="DatasetId"
Type="Placeholder"
OptionsEndPoint="get_datasets"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
OptionsEndPointParameters="WorkspaceId=[$WorkspaceId$]"
MultiSelectLimit="500"
/>
<Param Name="WorkspaceId"
Type="Placeholder"
OptionsEndPoint="get_workspaces"
OptionsEndPointValueColumn="id"
OptionsEndPointLabelColumn="name"
Options="Default="
ValueTemplate="<<{$value$}|~||~|groups/{$value$}/,FUN_IF_EMPTY>>"
MultiSelectLimit="500"
/>
</Params>
<Body>
{
"queries": [ { "query": "[$Query$]" } ],
"serializerSettings": { "includeNulls": true }
}
</Body>
</EndPoint>
</EndPoints>
<Tables>
<Table Name="Datasets"
SelectEndPoint="get_datasets"
LookupEndPoint="get_dataset"
DeleteEndPoint="delete_dataset"
/>
<Table Name="Workspaces"
SelectEndPoint="get_workspaces"
LookupEndPoint="get_workspace"
/>
<Table Name="[$parent.Table Name$]"
Expand="True"
SelectEndPoint="get_table_rows"
InsertEndPoint="insert_push_dataset_table_rows"
DataEndPoint="get_tables"
DataEndPointParameters="DatasetId=[$DatasetId$];WorkspaceId=[$WorkspaceId$]"
>
<Params>
<Param Name="WorkspaceId" Hidden="True"/>
<Param Name="DatasetId" Hidden="True"/>
<Param Name="TableName" Value="[$parent.Table Name$]" Hidden="True"/>
</Params>
</Table>
</Tables>
<Examples>
<!-- ====== EXAMPLE: Workspaces ====== -->
<Example Default="False" Group="ODBC" Label="Workspaces - Get Workspaces">
<Code>
<![CDATA[SELECT *
FROM Workspaces]]>
</Code>
</Example>
<!-- ====== EXAMPLE: a Workspace ====== -->
<Example Default="True" Group="ODBC" Label="Workspaces - Get a Workspace">
<Code>
<![CDATA[SELECT *
FROM Workspaces
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee']]>
</Code>
</Example>
<!-- ====== EXAMPLE: Datasets ====== -->
<Example Default="True" Group="ODBC" Label="Datasets - Get Datasets">
<Code>
<![CDATA[SELECT *
FROM Datasets]]>
</Code>
</Example>
<!-- ====== EXAMPLE: Datasets (in a specified Workspace) ====== -->
<Example Default="False" Group="ODBC" Label="Datasets - Get Datasets in a specified Workspace">
<Code>
<![CDATA[SELECT *
FROM Datasets
WITH (WorkspaceId = 'aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: a Dataset ====== -->
<Example Default="True" Group="ODBC" Label="Datasets - Get a Dataset">
<Code>
<![CDATA[SELECT *
FROM Datasets
WHERE Id='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee']]>
</Code>
</Example>
<!-- ====== EXAMPLE: create_push_dataset ====== -->
<Example Default="False" Group="ODBC" Label="Datasets - Create a Push Dataset">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: create_push_dataset (create 2 tables) ====== -->
<Example Default="False" Group="ODBC" Label="Datasets - Create a Push Dataset with 2 Tables">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: delete_dataset ====== -->
<Example Default="False" Group="ODBC" Label="Datasets - Delete a Dataset">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: refresh_dataset ====== -->
<Example Default="False" Group="ODBC" Label="Datasets - Refresh a Dataset">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: get_tables ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Tables">
<Code>
<![CDATA[SELECT *
FROM get_tables]]>
</Code>
</Example>
<!-- ====== EXAMPLE: get_tables (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Tables in a specified Dataset">
<Code>
<![CDATA[SELECT *
FROM get_tables
WITH (DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: get_table_columns ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Table Columns">
<Code>
<![CDATA[SELECT *
FROM get_table_columns
WITH (TableName='MyTable')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: get_table_columns (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Table Columns in a specified Dataset">
<Code>
<![CDATA[SELECT *
FROM get_table_columns
WITH (TableName='MyTable',
DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: SELECT FROM MyTable ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Table Rows (Use default Workspace and Dataset)">
<Code>
<![CDATA[SELECT *
FROM MyTable]]>
</Code>
</Example>
<!-- ====== EXAMPLE: SELECT FROM MyTable (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Table Rows for a specified Workspace and Dataset" >
<Code>
<![CDATA[
SELECT *
FROM get_table_rows
WITH(
"TableName"='Products'
, "DatasetId"='11b6c287-51d3-4061-bed8-811a4e5f6ce9'
, "WorkspaceId"='848353e2-f3b1-4fb4-89d7-44e84b8bdf9f'
)
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: SELECT FROM MyTable (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Insert / Update / Delete Rows for a specified Workspace and Dataset" >
<Code>
<![CDATA[
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"}]'
)
]]>
</Code>
</Example>
<!-- ====== IN-PROGRESS EXAMPLE: INSERT INTO MyTable ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Using an INSERT statement">
<Code>
<![CDATA[INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, 'Glass', true, '2001-02-03', 195.95)]]>
</Code>
</Example>
<!-- ====== IN-PROGRESS EXAMPLE: INSERT INTO MyTable (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Using an INSERT statement in a specified Dataset">
<Code>
<![CDATA[INSERT INTO MyTable(MyColumn1, MyColumn2, MyColumn3, MyColumn4, MyColumn5)
VALUES (1001, 'Glass', true, '2001-02-03', 195.95)
WITH (DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: get_table_rows ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Table rows">
<Code>
<![CDATA[SELECT *
FROM get_table_rows
WITH (TableName='MyTable')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: get_table_rows (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Get Table rows in a specified Dataset">
<Code>
<![CDATA[SELECT *
FROM get_table_rows
WITH (TableName='MyTable', DatasetId='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee')]]>
</Code>
</Example>
<!-- ====== EXAMPLE: truncate_push_dataset_table ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Truncate a Push Dataset Table">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: truncate_push_dataset_table (in a specified Dataset) ====== -->
<Example Default="False" Group="ODBC" Label="Tables - Truncate a Push Dataset Table in a specified Dataset">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: execute_dax_query ====== -->
<Example Default="False" Group="ODBC" Label="Execute a DAX query - Evaluating a Table">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: execute_dax_query ====== -->
<Example Default="False" Group="ODBC"
Label="Execute a DAX query - Using FILTER function with simple expression">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: execute_dax_query (using FILTER) ====== -->
<Example Default="False" Group="ODBC"
Label="Execute a DAX query - Using FILTER function with AND and OR operators">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: execute_dax_query (using SELECTCOLUMNS) ====== -->
<Example Default="False" Group="ODBC"
Label="Execute a DAX query - Selecting specific columns from a Table">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: execute_dax_query (using TOPN) ====== -->
<Example Default="False" Group="ODBC"
Label="Execute a DAX query - Selecting and sorting TOP N rows">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<!-- ====== EXAMPLE: generic_request ====== -->
<Example Default="False" Group="ODBC" Label="Execute a DAX query - A complicated query">
<Code>
<![CDATA[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
]]>
</Code>
</Example>
<Example Default="False" Group="ODBC" Label="Generics - A simple generic API request">
<Code>
<![CDATA[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
*/
]]>
</Code>
</Example>
<Example Default="False" Group="ODBC" Label="Generics - A generic API request with URL parameter">
<Code>
<![CDATA[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
*/
]]>
</Code>
</Example>
</Examples>
</ApiConfig>
