Forum Discussion
PowerBI doesn't update in adding new rows Excel
Hi, I have Excel sheet located in Sharepoint that's connected to the PowerBI report. This report is set for schedule refresh once everyday. However, on adding new rows to excel sheet doesn't reflect in PowerBI report after the schedule refresh. In first image, you can see I added row 17th but in 2nd image, the PowerBI report only shows till 16th row. Blurred details due to privacy of data.
Excel sheet image
Excel image
PowerBI image below
Not sure why it's not updating new rows. Although I do want to mention that when I make changes to existing row, say for example in row 9th, it does reflect. Please help!
10 Replies
- selpaqm
Helper V
Anonymous please check transform data and to see where the source, sheet or any table inside the sheet. Possibly the root cause is the specific area have been selected as source.
in case above sitution is not fitting you, if you run schedule refresh for the first time and getting this issue (especially if you have sql server connection) you need to edit all of your sources then schedule refresh reflect the data.
Hi selpaqm I checked in transform data and I see the correct table connected.
Also, I do not have any sql server connection. Since the excel sheet is in sharepoint, I don't need to setup any gateway, it's already connected since it's in cloud.- selpaqm
if the source is added as table, you have to expand your table area on excel. table end cell has little triangle at right bottom of the cell.
As a new user (6 months) to Power BI with the same question, I'm surprised by the answer often provided, which is to format the Excel data as an Excel Table instead of a conventional Excel worksheet. I don't often use Excel Tables and don't prefer using Tables - why is this not portrayed as a major fix needed for Power BI? If Power BI can import data from an Excel worksheet, can't it provide for the most basic of updates to that same worksheet (like adding rows)? Does Microsoft have in the works to address this? All that said, if I have built visuals via a conventional worksheet, and I wish to deal with the issue by changing to an Excel Table, do I need to start over and rebuild the visuals? Thank you.
cpwebbMicrosoft Employee
Power Query can recognise new rows being added to an Excel worksheet as well as an Excel table. However, there are situations where Excel workbooks (especially those generated by applications other than Excel as a result of an export) have incorrect information on where the data in a workbook is. There is an option in Power Query to deal with this problem: https://blog.crossjoin.co.uk/2021/01/17/new-option-to-solve-problems-with-power-query-not-loading-all-data-from-an-excel-worksheet/
