Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Updating Data with Push Push Data Sets

Hi Everyone 

I am a complete NOOB at this, thrown in the deep end on a project. I have followed the tutorial (https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data), and the solution works fine. I tried adding a column to the table (NumberSold) and set the initial value to 1, now I need to update that value to say 5. I cannot find a solution from the API reference, and all my attempts end up with new rows. Searched the forum and cannot see a solution. 

I surmise that I will have to delete all the rows and refresh, which could be a problem, given the ultimate size of the dataset, I am being asked to create. 

The code is 

private static void AddRows(string datasetId, string tableName)
{
string powerBIApiAddRowsUrl = String.Format("https://api.powerbi.com/v1.0/myorg/datasets/{0}/tables/{1}/rows", datasetId, tableName);

HttpWebRequest request = System.Net.WebRequest.Create(powerBIApiAddRowsUrl) 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));

//JSON content for product row
string rowsJson = "{\"rows\":" +
"[{\"ProductID\":1,\"Name\":\"Adjustable Race\",\"Category\":\"Components\",\"ManufacturedOn\":\"07/30/2015\",\"NumberSold\":5}," +
"{\"ProductID\":2,\"Name\":\"LL Crankarm\",\"Category\":\"Components\",\"ManufacturedOn\":\"07/30/2014\",\"NumberSold\":5}," +
"{\"ProductID\":3,\"Name\":\"HL Mountain Frame - Silver\",\"Category\":\"Bikes\",\"ManufacturedOn\":\"07/30/2014\",\"NumberSold\":5}]}";

//POST web request
byte[] byteArray = System.Text.Encoding.UTF8.GetBytes(rowsJson);
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("Rows Added");

}
}


 

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • Jayendran's avatar
    Jayendran
    Solution Sage
    6 years ago

    Hi Anonymous ,

     

    Unfortunately there are some less functionality available for Push DataSet API one of them is update the rows

     

    You can find the available functionality for Push DataSet API below

    https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets

     

    As per that only 2 functionality available for Rows ,either you can add new rows or delete rows 

    Datasets DeleteRows

    Datasets PostRows

     

    I'd suggest you to provide this functionality as a new idea in  Idea Forum

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Steps to push data into a dataset

    • Step 1: Register an app with Azure AD
    • Step 2: Get an authentication access token
    • Step 3: Create a dataset in Power BI
    • Step 4: Get a dataset to add rows into a Power BI table
    • Step 5: Add rows to a Power BI table

    The next section is a general discussion of Power BI API operations that push data.

    Power BI API operations to push data

    With the Power BI REST API, you can push data sources to Power BI. When an app adds rows to a dataset, dashboard tiles update automatically with the new data. To push data, use the PostDataSet and PostRows operations. To find a dataset, use the get DataSet operation. You can pass a group ID to work with a group for any of these operations. To get a group ID list, use the get groups operation.

    Here are the operations to push data into a dataset:

    • PostDataset
    • Get Datasets
    • Post Rows
    • Get Groups

    You create a dataset in Power BI by passing a JavaScript Object Notation (JSON) string to the Power BI service.

    The JSON string for a dataset has the following format:

    Power BI Dataset JSON object

    {"name": "dataset_name", "tables":
    [{"name": "", "columns":
        [{ "name": "column_name1", "dataType": "data_type"},
         { "name": "column_name2", "dataType": "data_type"},
         { ... }
        ]
      }
    ]
}

    For our Sales Marketing dataset example, you would pass a JSON string as shown below. In this example, SalesMarketing is the dataset name, and Product is the table name. After defining the table, you define the table schema. For the SalesMarketing dataset, the table schema has these columns: ProductID, Manufacturer, Category, Segment, Product, and IsCompete.

    Example dataset object JSON

    {
    "name": "SalesMarketing",
    "tables": [
        {
            "name": "Product",
            "columns": [
            {
                "name": "ProductID",
                "dataType": "int"
            },
            {
                "name": "Manufacturer",
                "dataType": "string"
            },
            {
                "name": "Category",
                "dataType": "string"
            },
            {
                "name": "Segment",
                "dataType": "string"
            },
            {
                "name": "Product",
                "dataType": "string"
            },
            {
                "name": "IsCompete",
                "dataType": "bool"
            }
            ]
        }
    ]
}

    I hope this information helps!

    Regards,

    Lewis

    Developer

    Apps4rent | O365CloudExperts

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Anonymous  - I did that, and each data insert, creates a new row. Ended up using a Gateway and refreshing for the historical data, and now looking at streaming for real-time. Thanks for the suggestion. 

       

      Regards