Newly added rows in Excel not captured in PowerBI .

[biswasg]

I created a PowerBI report by getting the data from an Excel file.

In the first instance all the rows were propoerly imported into PowerBI. However, after I updated the data in the excel file stored in the same location adding more rows of data, and refreshed PowerBI desktop, the additional rows do not show up.

I had a look at the Advance editor but could not spot any obvious mistake.

[dax]

Hi @biswasg , 

You could refer to @edhans 's suggestions. And you also need to check whether you save the change value in Excel, then try to click source step in Edit Queries, to check the file path whether is correct and click refresh to see whether you could view the new value. You also need to check whether the new value is filtered out. Or you could clear the cache, then refresh the report to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

Can you post the M code of your query? There could be a few things going on:

• You imported an Excel table (this is good) but the added data was added below the table and not actually in the table in Excel. If that is the case, the table has to be expanded to include the new data. When typing data, this happens automatically, but if data was pasted, you can sometimes cause the table to not autoexpand.
• Your M code has some limiter on the number of records being added
• Your M code has a filter that excludes data (some field is null for example) and the new data doesn't have that field populated, so it is being filtered out.

But without seeing some code, I cannot say for sure. Please use the code box when pasting code. It is the </> icon in the toolbar.

[ConradH]

I was experiencing the same problem as @biswasg . The solution was to turn the dataset in my excel sheet into a table. Afterwards, the PBI report picked up all the rows. Thanks for the help!

[biswasg]

Below is the copy of the Mcode. I have replaced some names of the folder by XXXX for privacy reason.

let
    Source = Excel.Workbook(File.Contents("C:\XXXX\XXXXX\OneDrive - XXXXX\NTD Annual report\Annual Report.xlsx"), null, true),
    Form1_Sheet = Source{[Item="Form1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Form1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Start time", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type any}, {"Should WHO regularly publish a consolidated report on all NTDs?", type text}, {"Why do you think the comprehensive report on NTDs will be useful? (multiple options possible from the list below).", type text}, {"How often should the report be published?", type text}, {"Statement 1", type any}, {"Statement 2", type any}, {"How often should the report be published?2", type any}, {"Where are you based?", type text}, {"In your opinion, who would be the potential user of the WHO NTD report? (choose all those you consider relevant)", type text}, {"Question", type any}, {"Print", type text}, {"Web", type text}, {"As an App", type text}, {"Statement 4", type any}, {"In addition to reporting on progress against the NTD road map indicators, should each report focus on a theme, eg the cross-cutting issues, vector control, etc?", type text}, {"If you suggested the report should have a theme, which of these areas would you suggest? (multiple selections possible).", type text}, {"If your answer to the previous question was no, please mention reasons. (choose all that apply)", type text}, {"Any other comment that can be useful in deciding on the report or its contents?", type text}, {"Please rank the form/media in which teh report should be pu", type any}, {"In which WHO Region are you based?", type text}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Email", "Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Should WHO regularly publish a consolidated report on all NTDs?", "Publish report"}, {"Why do you think the comprehensive report on NTDs will be useful? (multiple options possible from the list below).", "Why useful?"}, {"How often should the report be published?", "Frequency"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Statement 1", "Statement 2", "How often should the report be published?2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"In your opinion, who would be the potential user of the WHO NTD report? (choose all those you consider relevant)", "Potential users"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Question", "Statement 4"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"In addition to reporting on progress against the NTD road map indicators, should each report focus on a theme, eg the cross-cutting issues, vector control, etc?", "Theme"}, {"If you suggested the report should have a theme, which of these areas would you suggest? (multiple selections possible).", "Which themes?"}, {"If your answer to the previous question was no, please mention reasons. (choose all that apply)", "Not publish reasons"}, {"Any other comment that can be useful in deciding on the report or its contents?", "Comment"}, {"In which WHO Region are you based?", "Region"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Column25", "Column26", "Column27", "Column28", "Column29"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns3", "Completion time", "Completion time - Copy"),
    #"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column",{{"Completion time - Copy", "Survey date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Survey date", type date}})
in
    #"Changed Type1"

[edhans]

@biswasg - there is no filter in that code that would limit the rows. You are pulling a sheet vs a table, which can be troublesome. 

When you look at the step called "Source" do you see all of the data there, or does it stop? Are you 100% sure this is pointing to the right file? More than once in development, I've had the "live file" in the right place, but the file I was editing to change data was a copy I was messing with so Power BI was reading from a version I wasn't changing.