topic Re: Table Rows Limit in Developer

Table Rows Limit

RichardL — Thu, 20 Oct 2016 00:32:18 GMT

Hello, I'm creating a custom visual that reads data in from a table (csv file). My table has three columns, and each column has 59,000 rows. I am reading the rows in via TypeScript, but the latest version of Power BI desktop says "Too many values. Not showing all data. Click to see details". Publishing the report to a webpage shows that only 1,000 rows were read in because my console debug output says rows.length = 1000. I thought the rows limit was 30,000? Any thoughts on this? Thanks.

Re: Table Rows Limit

djnww — Thu, 20 Oct 2016 01:04:06 GMT

Hi @RichardL

In terms of row limits for a CSV, Power BI Desktop is only limited by the amount of memory that your machine has.

We currently load millions of rows of data into Power BI on a weekly basis without any issues.

Are you actually reading a physical CSV file , or is Power BI trying to read a file in TypeScript ?

Cheers,

Dan

Re: Table Rows Limit

RichardL — Thu, 20 Oct 2016 17:13:22 GMT

Hi @djnww

My 64-bit machine has 32 GB of memory and only uses around 8 GB when I'm running Power BI Desktop.

I'm reading a physical CSV file from disk in TypeScript. In my update(options: VisualUpdateOptions) method, I have

var dataView = options.dataViews[0];

var rows: DataViewTableRow = dataView.table.rows;

console.log('rows length: ', rows.length); // This would print out 1000

dataViewMapppings is defined as followed in capabilities.json:

"dataRoles": [
{
"displayName": "Values",
"name": "values",
"kind": 2
}
],

"dataViewMappings": [
{
"table": {
"rows": {
"for": {
"in": "values"
}
}
}
}
],

Do you see anything out of the ordinary here? Thanks.

Re: Table Rows Limit

djnww — Sat, 22 Oct 2016 04:31:31 GMT

Hi @RichardL,

I understand you're using TypeScript, but how is Power BI consuming it ? Via 'web' http ? In other words, what do you do when you select 'Get Data' ?

We have encoded data in JSON using python then into Power BI successfully Yes, we have had issues with maximum rows, but that has also been a server side limitation rather than Power BI. However, I have not see your error message before.

Cheers,

Re: Table Rows Limit

RichardL — Sat, 22 Oct 2016 17:29:07 GMT

Hi @djnww

I'm consumimg the data via 'web' http. The same row limit exists on both the web and in Power BI desktop. I see the limit of 1000 rows when I press F12 in Chrome or Edge to view console output.

Re: Table Rows Limit

v-chuncz-msft — Mon, 24 Oct 2016 07:53:23 GMT

@RichardL,

Based on my research, you need to specify dataReductionAlgorithm in capabilities.json. It describes how to reduce the amount of data exposed to the visual.

https://github.com/Microsoft/PowerBI-visuals/issues/44

Re: Table Rows Limit

RichardL — Mon, 24 Oct 2016 21:54:50 GMT

@v-chuncz-msft

Specifying dataReductionAlgorithm in capabilities.json did the trick. Thanks a bunch. I can now read in 30K rows. My dataViewMappings now looks like this:

"dataViewMappings": [
{
"table": {
"rows": {
"select": [
{ "for": { "in": "values" } }
],
"dataReductionAlgorithm": { "sample": { "count": 64000 } }
},
"rowCount": { "preferred": { "min": 2, "max": 100000 }, "supported": { "min": 1, "max": 100000 } }
}
}
],

This is good progress. However, almost half of my table still gets truncated. Didn't the Power BI team say they were raising the 30K rows limit?

Re: Table Rows Limit

ragnargylfa — Thu, 19 Oct 2017 11:28:36 GMT

For a newbie.

How/where do you edit capabilities.json? where is capabilities located?

Re: Table Rows Limit

RichardL — Thu, 19 Oct 2017 18:45:39 GMT

capabilities.json is located in the root folder of your project. Did you look?

Re: Table Rows Limit

ragnargylfa — Mon, 23 Oct 2017 13:47:13 GMT

Hello,

Thanks for your reply.

I did look. Im not sure where the root folder of the project is located or where is should look for that.

It is not in the folder where the power bi project file is stored.

Am I maybe misunderstanding something.

I am just importing data into Power BI using query ...

let
Source = Json.Document(Web.Contents("https://issues.companyname.com/rest/api/2/search?jql=project=PR&maxResults=3000")),
issues = Source[issues],

So perhaps this is not a project.. Like I said in the previous message I am a newbie 🙂

Is there a way to include more search results for a just a query?