步骤 4:获取数据集以向 Power BI 表中添加行Step 4: Get a dataset to add rows into a Power BI table

本文是将数据推送到数据集的分步演练的一部分。This article is part of a step-by-step walkthrough to push data into a dataset.

在将数据推送到数据集的步骤 3在 Power BI 中创建数据集)中,你调用了创建数据集操作以在 Power BI 中创建数据集。In step 3 of Push data into a dataset, Create a dataset in Power BI, you called the Create Dataset operation to create a dataset in Power BI. 在此步骤中,你将使用获取数据集操作和 Newtonsoft.Json 来获取数据集 ID。在步骤 4 中使用数据集 ID 向数据集添加行。In this step, you use the Get Datasets operation and Newtonsoft.Json to get a dataset id. You use the dataset id in step 4 to add rows to a dataset.

要将数据推送到 Power BI 数据集,需要引用数据集中的表。To push data into a Power BI dataset, you need to reference the table in the dataset. 要引用数据集中的表,首先需要获取数据集 IDTo reference a table in a dataset, you first need to get a Dataset ID. 使用获取数据集操作获取数据集 IDYou get a Dataset ID using the Get Dataset operation. 获取数据集操作将返回一个 JSON 字符串,它包含 Power BI 中所有数据集的列表。The Get Dataset operation returns a JSON string containing a list of all datasets in Power BI. 对 JSON 字符串进行反序列化的推荐的方法是使用 Newtonsoft.JsonThe recommended way to deserialize a JSON string is with Newtonsoft.Json.

下面介绍如何获取数据集。Here's how you get a dataset.

获取 Power BI 数据集Get a Power BI dataset

注意:在开始之前,先确保已按将数据推送到数据集演练中之前的步骤进行了操作。NOTE: Before you get started, make sure you have followed the previous steps in the push data into a dataset walkthrough.

  1. 在步骤 2:推送数据 - 获取身份验证访问令牌演练中创建的控制台应用程序项目中,安装 Newtonsoft.Json NuGet 包。In the Console Application project you created in Step 2: Walkthrough to push data, Get an authentication access token, install the Newtonsoft.Json NuGet package. 下面介绍了安装此程序包的方法:Here's how to install the package:

    a.a. 在 Visual Studio 2015 中,选择工具 > NuGet 包管理器 > 程序包管理器控制台In Visual Studio 2015, choose Tools > NuGet Package Manager > Package Manager Console.

    b.b. 包管理器控制台中,输入 Install-Package Newtonsoft.Json。In Package Manager Console, enter Install-Package Newtonsoft.Json.

  2. 安装包后,将 using Newtonsoft.Json; 添加到 Program.cs。After the package is installed, add using Newtonsoft.Json; to Program.cs.
  3. 在 Program.cs 中,添加以下代码以获取数据集 IDIn Program.cs, add the code below to get a Dataset ID.
  4. 运行控制台应用,并登录到你的 Power BI 帐户。Run the Console App, and login to your Power BI account. 应可在控制台窗口中看到数据集ID: 后跟 ID。You should see Dataset ID: followed by an id in the Console Window.

获取数据集示例Sample get a dataset

将此代码添加到 Program.cs。Add this code into Program.cs.

  • 在 static void Main (string[] args) 中:In static void Main(string[] args):

    static void Main(string[] args)
    {
    
      //Get an authentication access token
      token = GetToken();
    
      //Create a dataset in Power BI
      CreateDataset();
    
      //Get a dataset to add rows into a Power BI table
      string datasetId = GetDataset();
    }
    
  • 添加 GetDatset() 方法 ︰Add a GetDatset() method:

      #region Get a dataset to add rows into a Power BI table
      private static string GetDataset()
      {
          string powerBIDatasetsApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets";
          //POST web request to create a dataset.
          //To create a Dataset in a group, use the Groups uri: https://api.PowerBI.com/v1.0/myorg/groups/{group_id}/datasets
          HttpWebRequest request = System.Net.WebRequest.Create(powerBIDatasetsApiUrl) as System.Net.HttpWebRequest;
          request.KeepAlive = true;
          request.Method = "GET";
          request.ContentLength = 0;
          request.ContentType = "application/json";
    
          //Add token to the request header
          request.Headers.Add("Authorization", String.Format("Bearer {0}", token));
    
          string datasetId = string.Empty;
          //Get HttpWebResponse from GET request
          using (HttpWebResponse httpResponse = request.GetResponse() as System.Net.HttpWebResponse)
          {
              //Get StreamReader that holds the response stream
              using (StreamReader reader = new System.IO.StreamReader(httpResponse.GetResponseStream()))
              {
                  string responseContent = reader.ReadToEnd();
    
                  //TODO: Install NuGet Newtonsoft.Json package: Install-Package Newtonsoft.Json
                  //and add using Newtonsoft.Json
                  var results = JsonConvert.DeserializeObject<dynamic>(responseContent);
    
                  //Get the first id
                  datasetId = results["value"][0]["id"];
    
                  Console.WriteLine(String.Format("Dataset ID: {0}", datasetId));
                  Console.ReadLine();
    
                  return datasetId;
              }
          }
      }
      #endregion
    

下一步将演示如何向 Power BI 表中添加行The next step shows you how to add rows to a Power BI table.

下面是完整代码清单Below is the complete code listing.

完整代码清单Complete code listing

using System;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System.Net;
using System.IO;
using Newtonsoft.Json;

namespace walkthrough_push_data
{
    class Program
    {
        private static string token = string.Empty;

        static void Main(string[] args)
        {

            //Get an authentication access token
            token = GetToken();

            //Create a dataset in Power BI
            CreateDataset();

            //Get a dataset to add rows into a Power BI table
            string datasetId = GetDataset();

        }

        #region Get an authentication access token
        private static string GetToken()
        {
            // TODO: Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.21.301221612
            // and add using Microsoft.IdentityModel.Clients.ActiveDirectory

            //The client id that Azure AD created when you registered your client app.
            string clientID = "{Client_ID}";

            //RedirectUri you used when you register your app.
            //For a client app, a redirect uri gives Azure AD more details on the application that it will authenticate.
            // You can use this redirect uri for your client app
            string redirectUri = "https://login.live.com/oauth20_desktop.srf";

            //Resource Uri for Power BI API
            string resourceUri = "https://analysis.windows.net/powerbi/api";

            //OAuth2 authority Uri
            string authorityUri = "https://login.windows.net/common/oauth2/authorize";

            //Get access token:
            // To call a Power BI REST operation, create an instance of AuthenticationContext and call AcquireToken
            // AuthenticationContext is part of the Active Directory Authentication Library NuGet package
            // To install the Active Directory Authentication Library NuGet package in Visual Studio,
            //  run "Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory" from the nuget Package Manager Console.

            // AcquireToken will acquire an Azure access token
            // Call AcquireToken to get an Azure token from Azure Active Directory token issuance endpoint
            AuthenticationContext authContext = new AuthenticationContext(authorityUri);
            string token = authContext.AcquireToken(resourceUri, clientID, new Uri(redirectUri)).AccessToken;

            Console.WriteLine(token);
            Console.ReadLine();

            return token;
        }

        #endregion

        #region Create a dataset in Power BI
        private static void CreateDataset()
        {
            //TODO: Add using System.Net and using System.IO

            string powerBIDatasetsApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets";
            //POST web request to create a dataset.
            //To create a Dataset in a group, use the Groups uri: https://api.PowerBI.com/v1.0/myorg/groups/{group_id}/datasets
            HttpWebRequest request = System.Net.WebRequest.Create(powerBIDatasetsApiUrl) as System.Net.HttpWebRequest;
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;
            request.ContentType = "application/json";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            //Create dataset JSON for POST request
            string datasetJson = "{\"name\": \"SalesMarketing\", \"tables\": " +
                "[{\"name\": \"Product\", \"columns\": " +
                "[{ \"name\": \"ProductID\", \"dataType\": \"Int64\"}, " +
                "{ \"name\": \"Name\", \"dataType\": \"string\"}, " +
                "{ \"name\": \"Category\", \"dataType\": \"string\"}," +
                "{ \"name\": \"IsCompete\", \"dataType\": \"bool\"}," +
                "{ \"name\": \"ManufacturedOn\", \"dataType\": \"DateTime\"}" +
                "]}]}";

            //POST web request
            byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(datasetJson);
            request.ContentLength = byteArray.Length;

            //Write JSON byte[] into a Stream
            using (Stream writer = request.GetRequestStream())
            {
                writer.Write(byteArray, 0, byteArray.Length);

                var response = (HttpWebResponse)request.GetResponse();

                Console.WriteLine(string.Format("Dataset {0}", response.StatusCode.ToString()));

                Console.ReadLine();
            }
        }
        #endregion

        #region Get a dataset to add rows into a Power BI table
        private static string GetDataset()
        {
            string powerBIDatasetsApiUrl = "https://api.powerbi.com/v1.0/myorg/datasets";
            //POST web request to create a dataset.
            //To create a Dataset in a group, use the Groups uri: https://api.PowerBI.com/v1.0/myorg/groups/{group_id}/datasets
            HttpWebRequest request = System.Net.WebRequest.Create(powerBIDatasetsApiUrl) as System.Net.HttpWebRequest;
            request.KeepAlive = true;
            request.Method = "GET";
            request.ContentLength = 0;
            request.ContentType = "application/json";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            string datasetId = string.Empty;
            //Get HttpWebResponse from GET request
            using (HttpWebResponse httpResponse = request.GetResponse() as System.Net.HttpWebResponse)
            {
                //Get StreamReader that holds the response stream
                using (StreamReader reader = new System.IO.StreamReader(httpResponse.GetResponseStream()))
                {
                    string responseContent = reader.ReadToEnd();

                    //TODO: Install NuGet Newtonsoft.Json package: Install-Package Newtonsoft.Json
                    //and add using Newtonsoft.Json
                    var results = JsonConvert.DeserializeObject<dynamic>(responseContent);

                    //Get the first id
                    datasetId = results["value"][0]["id"];

                    Console.WriteLine(String.Format("Dataset ID: {0}", datasetId));
                    Console.ReadLine();

                    return datasetId;
                }
            }
        }
        #endregion
    }
}

下一步 >Next Step >

后续步骤Next steps

向 Power BI 表中添加行Add rows to a Power BI table
Newtonsoft.JsonNewtonsoft.Json
获取数据集Get Datasets
将数据推送到 Power BIPush data into Power BI
Power BI REST API 概述Overview of Power BI REST API
Power BI REST API 引用Power BI REST API reference

更多问题?More questions? 尝试参与 Power BI 社区Try the Power BI Community