SharePoint Online Excel Dataset Missing New Rows After Refresh

[mjcarrabine]

I am having issues with a dataset in PowerBI not including new rows from an Excel file saved on SharePoint Online. Please help.

We have a remote location with SQL server, and I wanted to report off of a few tables using Power BI.

On 24 March, I created an Excel file with 7 worksheets, and each one has an Excel table that queries one of the SQL tables I need. I also added a few relationships. I saved this Excel file on SharePoint Online. In PowerBI, I added a dataset pointing to this Excel file on SharePoint online, created a report, and a dashboard, and everything was great. Please note, I am not trying to have PowerBI access the SQL Server, I only want to report off of the data stored in the Excel file.

For reference, these are the rowcounts of 3 of the worksheets: 

• Drivers: 108 rows
• Orders: 499 rows
• Tickets: 349 rows

Today, I logged into the remote PC, opened the Excel file, clicked Data --> Refresh All to requery the data from SQL, and saved uploaded the file with the same name to SharePoint Online. The rowcounts in the Excel file are now:

• Drivers: 126 rows
• Orders: 555 rows
• Tickets: 476 rows

Then I went to PowerBI, and refreshed the dataset. However, I am not seeing any of the rows added since 24 March. The dataset says it was refreshed successfully, and there is no error message. 

I assumed there was an issue with this file so I saved the Excel file with a different name on SharePoint Online and created a new dataset in PowerBI. I am again getting the original 24 March rowcounts. I double-checked that the named table ranges include the new rows.

I can see 126 rows in the Driver worksheet in the Excel file. How does this new dataset from the new file not have 126 rows? Is there another layer in between the rows in the worksheet and what PowerBI is importing?  

Thank you,

Mike

[v-haibl-msft]

@mjcarrabine

Can you find the new rows in Query Editor? Are you using the latest Apr 2017 version of PBI Desktop?

Best Regards,
Herbert

[mjcarrabine]

Hello Herbert,

I am not using PowerBI Desktop.

I am using Excel to query the data from SQL, saving that file to SharePoint Online, and then use the Excel file as the source for my Dataset in PowerBI online. 

I just remoted into the remote PC and opened the Excel file again, and I clicked refresh all so I am seeing more rows in the Drivers worksheet in Excel. Then on the Query tab in the ribbon, I clicked Edit. In the Query Editor at the top there is a message saying "This preview may be up to 15 days old. Refresh?" And then I refreshed it, saved the Excel file with a new name to SharePoint Online. Then in PowerBI, I created a new dataset with the excel file as the source, but I am still only seeing rows up to 24 March.

Can you help clarify what in the Excel file is PowerBI looking at that I need to refresh that is showing different records than the data in the worksheet?

Thank you for your help.

Thank you,

Mike

[mjcarrabine]

So, I figured out how to get this to work in my situation:

• If I go to Data --> Refresh All, it refreshes the data in the work sheets. However, PowerBI does not see the additional rows.
• If I go to Data --> Manage Data Model --> Refresh All, and save, then PowerBI does see the new rows.

Is this the expected behavior? Is there another way I should be doing this?

Thank you,

Mike

[mjcarrabine]

Is this the expected behavior? Is there another way I should be doing this?

Is there additional info needed?

Thank you,

Mike