Power BI - Rows Missing in Excel File in Mailbox

[Yoshimitsu411]

Hello All

This is a very interesting one. I have an excel file that is in a Outlook 365 shared mailbox which I am connecting to and bringing in the data to my Power BI report.

There are 7250 rows in the Excel file but in my Power BI report I only see 6563 rows. 

I have tried the following:

• Created a CountRows measure to count the total rows
• Removed all filters from the Excel file as well as in Power Query
• Hit Refresh several times in Power BI desktop as well as Power Query
• Made sure that "Enable Load" option in Power Query for the table is ticked
• Downloaded the Excel file to my local drive and created a new report. This shows all 7250 rows after refreshing several times.
• Tried to keep the bottom 500 rows which did not show the missing rows
• Added an Index column to assist with finding the missing rows

This is a standalone table in the report that has 5 other tables. There is no relationship to the other tables

It seems the refresh preview option is not bringing in all the rows. This is a very important report and must have all the data.

Any suggestions?

Thank in advance.

Yoshi

[Anonymous · Accepted solution]

have you doble checked to make sure that the excel data is in a table format? or all the rows are in a table format?

[scancela]

Hello,

I do have a similar issue. I have a simple Excel File. I checked it in the Editor Query and all rows are shown. However, at the table view in the frontend, some rows are missing.

I attached an excel file to check it.

Happy to know what´s going on or what I´m doing wrong.

Cheers

https://timbertecsa-my.sharepoint.com/:x:/g/personal/administrador_timbertecsa_onmicrosoft_com/EVQf4... 

[Yoshimitsu411]

The data in the excel file is not formatted as a table. Is this necessary when you have a large number of rows?

I created a separate report with a local copy of the same excel file and it brings in all the rows, after refreshing several times.

Thanks

[v-lili6-msft]

hi  @Yoshimitsu411 

If you try to use CTRL+T to make all these data in Excel Tables and then try it again.

Regards,

Lin

[Yoshimitsu411]

I cannot format as a table as the excel file is auto generated by an application and sent to the Outlook 365 shared mailbox. I will need to write a FLOW to do this and I'm not interested in this as it will involve having to manage the FLOW should it break.

I have found one solution which is to remove all formatting in the excel file by selecting all the rows and then Clear > Clear Formats under the Editing tab. The Power BI report now brings in all the rows.

I will test this again this week to make sure this solution works. 

Thanks for all your suggestions regarding formatting. It definitely set me on the right path.

Yoshi

[Yoshimitsu411]

I'm still having trouble with this issue. Any new rows added to the auto generated Excel file are not showing up in Power BI.

It defintely has to do with the formatting of the Excel file and I'm wondering if there is a way to remove the formatting in Power BI.

Any other suggestions?

Thanks

[Yoshimitsu411]

Hi All

I have tested this on a colleagues computer with the same version of Power BI desktop and he is seeing all the rows.

For some reason Power BI desktop on my computer which is the same version is not showing all the rows. Is it possibly showing cached data?

[Yoshimitsu411]

The problem has been solved. I got the Admin to format the data as a table from where it is being generated, and sent to the shared mailbox.

Thanks for all your help and suggestions.

Yoshi

[amitchandak]

@Yoshimitsu411 , does it show any error in yellow color in edit query/data transformation mode

if you see any error sign

refer, how to handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

[Yoshimitsu411]

Thanks @amitchandak, I checked this. The data loads correctly without any errors, it just doesn't bring in all the rows from the Excel file.

The excel file is stock with no formulas. It is just bizarre that Power BI won't bring in all the rows and I'm stuck!

Thanks again.