@paulinuk2017 wrote:
Hello
Does anyone know of an start to finish sample for power bi api?
I need to get data in real time and also to clear existing data from a data set in my dashboard
Most of the samples I have seen are not clear on the rest application side
I need to get data from my azure database and put this into the dataset
For example if a set of records is added at 14:00 then 14:05 I need to totally clear all records from the table just before I insert the 14:05 records
Paul
@paulinuk2017
To clear all records from a table, you can call the API Delete Rows before adding rows. See demo below.
using System;
using System.Net;
//Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
using Microsoft.IdentityModel.Clients.ActiveDirectory;
//Install-Package Newtonsoft.Json
using Newtonsoft.Json;
using System.IO;
using System.Web;
using System.Collections.Specialized;
namespace ConsoleApplication39
{
class Program
{
//Step 1 - Replace {client id} with your client app ID.
//To learn how to get a client app ID, see Register a client app (https://msdn.microsoft.com/en-US/library/dn877542.aspx#clientID)
private static string clientID = "{client_id}";
//RedirectUri you used when you registered your app.
//For a client app, a redirect uri gives AAD more details on the specific application that it will authenticate.
private static string redirectUri = "https://login.live.com/oauth20_desktop.srf";
//Resource Uri for Power BI API
private static string resourceUri = "https://analysis.windows.net/powerbi/api";
//OAuth2 authority Uri
private static string authority = "https://login.windows.net/common/oauth2/authorize";
//the account used to login Power BI
private static string username = "your power bi account";
private static string password = "your password";
private static AuthenticationContext authContext = null;
private static string token = String.Empty;
//The power bi app workspace id(the GUID after /groups/ in below link
//when viewing a dataset in Power BI Service, the link is like
private static string groupId = "group id";
//The target datasetId
private static string datasetId = "dataset id";
//The target table name
private static string tableName = "table name";
static void Main(string[] args)
{
//token = getAccessTokenWithLoginPopUp();
token = getAccessTokenSilently();
clearTableData(groupId, datasetId,tableName);
Console.ReadKey();
}
static void clearTableData(string groupId, string datasetId,string tableName)
{
HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp(String.Format("https://api.powerbi.com/v1.0/myorg/groups/{0}/datasets/{1}/tables/{2}/rows", groupId, datasetId, tableName));
//POST web request to create a datasource.
request.KeepAlive = true;
request.Method = "DELETE";
request.ContentLength = 0;
//Add token to the request header
request.Headers.Add("Authorization", String.Format("Bearer {0}", token));
//Write JSON byte[] into a Stream
using (Stream writer = request.GetRequestStream())
{
var response = (HttpWebResponse)request.GetResponse();
Console.WriteLine("DELETE ROWS", response.StatusCode.ToString());
}
}
static string getAccessTokenSilently()
{
HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp("https://login.windows.net/common/oauth2/token");
//POST web request to create a datasource.
request.KeepAlive = true;
request.Method = "POST";
request.ContentLength = 0;
request.ContentType = "application/x-www-form-urlencoded";
//Add token to the request header
request.Headers.Add("Authorization", String.Format("Bearer {0}", token));
NameValueCollection parsedQueryString = HttpUtility.ParseQueryString(String.Empty);
parsedQueryString.Add("client_id", clientID);
parsedQueryString.Add("grant_type", "password");
parsedQueryString.Add("resource", resourceUri);
parsedQueryString.Add("username", username);
parsedQueryString.Add("password", password);
string postdata = parsedQueryString.ToString();
//POST web request
byte[] dataByteArray = System.Text.Encoding.ASCII.GetBytes(postdata); ;
request.ContentLength = dataByteArray.Length;
//Write JSON byte[] into a Stream
using (Stream writer = request.GetRequestStream())
{
try
{
writer.Write(dataByteArray, 0, dataByteArray.Length);
var response = (HttpWebResponse)request.GetResponse();
var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
dynamic responseJson = JsonConvert.DeserializeObject<dynamic>(responseString);
return responseJson["access_token"];
}
catch (WebException ex)
{
using (WebResponse response = ex.Response)
{
HttpWebResponse httpResponse = (HttpWebResponse)response;
Console.WriteLine("Error code: {0}", httpResponse.StatusCode);
using (Stream data = response.GetResponseStream())
using (var reader = new StreamReader(data))
{
string text = reader.ReadToEnd();
Console.WriteLine(text);
}
}
}
}
return null;
}
}
}
