Datasets.Extensions.PostRowsAsync() - param documentation?

[dandy72]

Hi all,

I'm trying to use PostRowsAsync(), documented here:

https://docs.microsoft.com/en-us/dotnet/api/microsoft.powerbi.api.v1.datasetsextensions.postrowsasyn...

It's got a "requestMessage" parameter of type "Object", and that's all the documentation there is to it.  While it can be inferred this is part of an HTTP request, I have no idea with it can/needs to be set to, or its format, encoding, etc.  I also can't find any sample using this library, so I'm left guessing.

I've used many other classes/functions from this library and I've generally been able to make good use of it based on a few samples on GitHub, but none I've come across use this particular function (or its non-async version, PostRow()).

I'm making the assumption that the link above is part of the documentation for the code at:

https://github.com/Microsoft/PowerBI-CSharp

Digging into that source, I can see PostRowsAsync() invokes IDatasets.PostRowsWithHttpMessagesAsync().  In turn, I can see the function signature defined in:

https://github.com/Microsoft/PowerBI-CSharp/blob/master/sdk/PowerBI.Api/Source/V1/IDatasets.cs

...but no code showing me what the library is internally doing with this parameter.  The parameter comments in this file shed very little extra light on "requestMessage", simply stating it's "the headers that will be added to request", which isn't terribly handy as I still don't know what's expected from those headers (and again, format, encoding etc).

Grasping at straws, I did find the implementation for PostRowsWithHttpMesagseAsync() inside Datasets.cs under the **V2** folder (not V1):

https://github.com/Microsoft/PowerBI-CSharp/blob/master/sdk/PowerBI.Api/Source/V2/Datasets.cs

The code starting at around line 429 shows requestMessage is assumed to be convertable into a JSON string and then serialized (if I'm reading this right), but again, I'm not terribly confident of what should be passed in.  I've tried a raw JSON string based on samples that invoke the REST API directly to add rows to a dataset (rather than using this library), but the errors I've gotten back from my trials and experiments aren't really indicating whether my parameter is valid or not; it seems like I've probably got other errors to deal with *regardless* of what I pass in (I get the same errors even if I provide null--so the code is probably not getting far enough to even start looking at the param).

Finally (and I realize I'm all over the place here)...the content of "Power BI Azure Embedded Readme.md" at:

https://github.com/Microsoft/PowerBI-CSharp

...starts with a comment saying this library is now being deprecated in favor of the "...One Power BI API using powerbi.com".  My (very rough) understanding is that there's multiple SDKs that are being merged, which sounds to me like it's probably a good thing, but I now have no idea what the replacement for this library is (if there even is one at this point in time).

Should I even continue trying to get this parameter figured out?  If not, what's the replacement for this now-deprecated library?  Should I abandon the whole thing and instead go directly to the REST API?  I'd hate to have to resort to that, as this is about the last thing I still needed to figure out before my project has all the basic required components completed, and the library's a rather nice wrapper around the raw REST API.  But if it's being abandoned, I need an alternative no matter what...

Any guidance?

[AlanBourke]

Over a year after the original post and there are still no comprehensive examples of API V2 async methods anywhere that I can find. Bad.

[Eric_Zhang · Accepted solution]

@dandy72

See a demo which works in my test.

By the way, there's no replacement for the SDK, as the collectionname and workspaceid are the concepts in the being deprecated Power BI Embedded. The new "embedding-with-non-power-bi-users" is embeding reports from Power BI Service instead of Azure PBI workspace.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.PowerBI.Api.V1;
using Microsoft.Rest;
using Microsoft.PowerBI.Api.V1.Models;

namespace ConsoleApplication39
{
    class Program
    {

        static string accesskey = "KJixsmmw+Ncxxxxx23xxxxxxcaOIFr6a2vmQ==";
        static string workspaceCollectionName = "cisxxxxexo";
        static string workspaceId = "79c71931-dexxxxa3cxxxd2192fe0b";
        static void Main(string[] args)
        {

            var credentials = new TokenCredentials(accesskey, "AppKey");

            // Instantiate your Power BI client passing in the required credentials
            var client = new PowerBIClient(credentials);

            // Override the api endpoint base URL.  Default value is https://api.powerbi.com
            client.BaseUri = new Uri("https://api.powerbi.com"); 

            //create a dataset and get datasetkey
            string datasetID =  CreateDatasets(workspaceCollectionName, workspaceId,client);

            string data = @"{  ""rows"":  
                                [
                                    { ""id"": 1, ""name"": ""Tom""},                                
                                    { ""id"": 2, ""name"": ""Jerry""}
                                ]
                            }";

            object dataObj = Newtonsoft.Json.JsonConvert.DeserializeObject<Object>(data.ToString());

            PostRows(workspaceCollectionName,workspaceId,datasetID,"testtable", dataObj, client); 
             
        }


        static   void PostRows(string workspaceCollectionName, string workspaceId, string datasetid, string tableName, object datajson, PowerBIClient client )
        {
              //public static object PostRows(this IDatasets operations, string collectionName, string workspaceId, string datasetKey, string tableName, object requestMessage);
               var response =   client.Datasets.PostRows(workspaceCollectionName, workspaceId, datasetid, tableName,datajson);


        }

        static string CreateDatasets(string workspaceCollectionName, string workspaceId, PowerBIClient client)
        {
              
            Dataset ds = new Dataset();
                ds.Name = "testdataset";
                Table table1 = new Table();
                table1.Name = "testTable";
                

                Column column1 = new Column("id","Int64");
                Column column2 = new Column("name", "string");

                table1.Columns = new List<Column>() { column1, column2 }; 

                ds.Tables = new List<Table>() { table1}; 

                var response =   client.Datasets.PostDataset(workspaceCollectionName, workspaceId, ds);

                dynamic obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Object>(response.ToString());
                return obj["id"].ToString(); 
        }

       

    }
}

[dandy72]

Hmmm...I suspect that, technically speaking this is exactly the answer to the question that I asked - but ultimately I'm running into something else that I think will force me to rethink my approach.  I'm running into this "Data [dataset id] is not Push API dataset" error that others have reported.

My understanding is that you can't add rows to a dataset that exists in the service when this dataset originates from a .pbix file that was uploaded.  If you create a dataset with the REST API, adding rows works, but for a dataset coming from a .pbix, it'll result in this "...not Push API dataset" error.

I used the desktop version of Power BI to create my dataset, and some reports.  That got saved in a local .pbix file.  I then need to upload the whole thing, and update the data on a regular basis.  While I could create the dataset from scratch through the REST API, it's not clear to me how I could get the locally-created reports uploaded on their own, if they have to be separated from the dataset--and then the next problem would be to somehow link the reports with the dataset.

I've looked at getting my local .pbix file refreshed programmatically (and then re-uploading the whole thing, after deleting anything that might already have been uploaded previously), but it looks like there's no API to do this.

What's the approach people take to do this sort of thing?  Seems to me the end goal is pretty simple:  A local .pbix defines reports, I want them uploaded to the service, and I want to programmatically pump new data into the dataset on a regular basis.

[rajusharma]

Hi Dandy72, 

While reading the comments I am exactly in same situation like you explained below. Were you able to solve the problem ? Please share your idea if you have implemented according to your workflow? 

[AlanBourke]

This works, assuming you have the ID of the PowerBI dataset you're updating, the table name in the dataset, and your auth token:

       public async Task<object> AddRowsToTableAsync(string datasetId, string tableName, object rows, string token)
        {
            var tokenCredentials = new TokenCredentials(token, "Bearer");
            var ret = new object();
            using (var c = new PowerBIClient(new Uri(PowerBIDatasetsApiUrl), tokenCredentials))
            {
               ret = await c.Datasets.PostRowsAsync(datasetId, tableName, rows);
            }
    
            return ret;
        }

Where for example 'rows' is a List<SerializableMyDataRow>, and SerializableMyDataRow is:

public class SerializableMyDataRow
{
    [JsonProperty("Account")]
    public string Account { get; set; }

    [JsonProperty("Reference")]
    public string Reference { get; set; }

    [JsonProperty("Value")]
    public decimal Value { get; set; }

}

The properties there match the column names and types in the PowerBI table.

[dandy72]

Thanks, this is exactly what I needed.  I should be able to complete my project with this.

I'm still not 100% clear on the distinctions between what's available right now (and moving forward), so once I get a bit of downtime, I'll try to figure it out and redo those parts of my project based on what should be the most appropriate in the longer term.