Only a few rows are being shown in report despite there being a few hundred in the data.

[Tiptop96]

I have importerad an excel file into power BI. Clicking the data tab on the left in power BI desktop shows me that there are a few hundred rows of data. But when I pull all columns into a plain table in the report it only shows a few rows. After some experimentation I noticed that depending on what columns were selected the amount of rows being displayed shifted and that it decreased a lot once a column with numbers was selected. Why is this happening?

[MFelix · Accepted solution]

Hi @Tiptop96 ,

this has to do with the type of summarization each column assumes, when you have numbers (whole or decimal) by default the summarization is sum, whne it's text the default is not summarize,

If you have a simple data table:

Cat  Value

A       10

A       10

B       10

B       10

C       10

The resul of placing this on a table on the PBI it will be

Cat  SUM VALUE

A       20

B       20

C       10

The numbers are the but the number of lines are reduced because A and B rows are sum togetherfor each one.

You can change the default summarization on the modeling tab for each column or on the table/visual data check the video:

Regards,

MFelix

[v-juanli-msft · Accepted solution]

Hi @Tiptop96 

MFelix's suggestion should be helpful.

Please click on the "down" arrow on the column field as below to check if your column is summarized or not.

To make it show all rows, please select "don't summarized"

If all above don't help you, please show a screenshot let me know more details.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @Tiptop96 ,

this has to do with the type of summarization each column assumes, when you have numbers (whole or decimal) by default the summarization is sum, whne it's text the default is not summarize,

If you have a simple data table:

Cat  Value

A       10

A       10

B       10

B       10

C       10

The resul of placing this on a table on the PBI it will be

Cat  SUM VALUE

A       20

B       20

C       10

The numbers are the but the number of lines are reduced because A and B rows are sum togetherfor each one.

You can change the default summarization on the modeling tab for each column or on the table/visual data check the video:

Regards,

MFelix

[Anonymous]

I had somethng similar the pther day.

A table visualisation show redundent, empty, rows. depends on the columns selected.

Will be happy to know as well.