Power Query not identifying all rows from excel unless I resort in excel and save???

Hi dheslin,

Where are the files stored?

What validation steps did you use in Power BI to confirm all rows weren't loading?

Can you post your power query code (remove anything sensitive)?

I'm sure we can get to the bottom of it with a little more detail.

Hi,

sorry to resurect this topic but, I have a really similar problem.

I can send an example file and the steps to reproduce.

First de xls file: https://www.manxa.com/01simple.xls

The steps:

1.- open excel (tested on 2019 and 365) with a new blank spreadsheet

2.- goto: data -> get data -> from file -> from excel file

3.- in data preview you only see two cells with text

4.- the same when you finishes de wizard ...

If you opens the file normally, you see some more data ...

Ok, this case is very simple, but it's the same what happens with another xls file with more real data.

Anyone can help us?

Thank's!

Good Morning, 

In answer to your questions.

The files are stored on a business server that is connected to Power BI via a gateway that is set t refresh daily.

I created a matrix that shows the specific information Ie row count and some specifics aound the data. When i check the row count of populated in the excel file I can compare the row count with this number.

Its really strange when I sort the information largest to smallest on a column with no duplicated information and save the file the numbers will then match.

Here is the query code:

let

    Source = Excel.Workbook(File.Contents("*Source location removed*\List of Orders.xlsx"), null, true),

    #"Maintenance Orders_Sheet" = Source{[Item="Maintenance Orders",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(#"Maintenance Orders_Sheet", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order Number", Int64.Type}, {"Priority", Int64.Type}, {"Priority Descr", type text}, {"Equipment Nb", Int64.Type}, {"Equipment Descr", type text}, {"Description", type text}, {"Location and Account Assignment for Technical Object", Int64.Type}, {"ILOA Individual", type text}, {"System Condition Descr", type text}, {"Maintenance Planning Plant", Int64.Type}, {"Planner Group for Customer Service and Plant Maintenance", type text}, {"Object ID of the Work Center Descr", type text}, {"Maintenance Plan", Int64.Type}, {"Maintenance Plan Call Nb", type text}, {"Maintenance Item", Int64.Type}, {"Order Nb", Int64.Type}, {"PM Order Reference Date", type date}, {"Time of Reference Date", type datetime}, {"Maintenance Processing Phase", type text}, {"Maintenance Activity Type", type text}, {"Notification Nb", type any}, {"Material Nb", type any}, {"Order Type", type text}, {"Order Type Descr", type text}, {"Reference Order Nb", type any}, {"Entered By", type text}, {"Created On", type date}, {"Last changed by", type text}, {"Change date for Order Master", type date}, {"Company Code", Int64.Type}, {"Plant", Int64.Type}, {"Plant Descr", type text}, {"Responsible Cost Center", Int64.Type}, {"Location Plant", Int64.Type}, {"Release Date", type date}, {"Technical Completion Date", type date}, {"Close Date", type date}, {"Object Nb", type text}, {"Profit Center", type text}, {"Time Created", type datetime}, {"Changed At", type datetime}, {"Main Work Center for Maintenance Tasks", type text}, {"Cost Center", Int64.Type}, {"Functional Location", type text}, {"ABC Indicator for Technical Object", type text}, {"Maintenance Plant", Int64.Type}, {"Start Date", type date}, {"Finish Date", type date}, {"Scheduled Release Date", type date}, {"Scheduled Start", type date}, {"Scheduled Finish", type date}, {"Actual Start Date", type date}, {"Actual Finish Date", type any}, {"Confirmed Order Finish Date", type date}, {"Actual Release Date", type date}, {"Planned Release Date", type any}, {"Nb of Reservation dependent Requirements", Int64.Type}, {"Total Order Quantity", Int64.Type}, {"Task List Type", type text}, {"Key for Task List Group", Int64.Type}, {"Responsible Planner Group", type text}, {"Application of the Task List", type text}, {"Group Counter", Int64.Type}, {"Task List Usage", Int64.Type}, {"Valid From Date", type date}, {"Basic Start Time", type time}, {"Basic Finish Time", type text}, {"Scheduled Start Time", type text}, {"Scheduled Finish Time", type text}, {"Actual Start Time", type time}, {"Confirmed Order Finish Time", type time}}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Maintenance Planning Plant", type text}, {"Order Nb", type text}, {"Notification Nb", type text}, {"Material Nb", type text}, {"Reference Order Nb", type text}, {"Company Code", type text}, {"Plant", type text}, {"Responsible Cost Center", type text}, {"Location Plant", type text}, {"Cost Center", type text}, {"Maintenance Plant", type text}, {"Actual Finish Date", type date}, {"Planned Release Date", type date}, {"Nb of Reservation dependent Requirements", type text}, {"Key for Task List Group", type text}, {"Group Counter", type text}, {"Task List Usage", type text}, {"Basic Finish Time", type text}, {"Order Number", type text}, {"Equipment Nb", type text}, {"Location and Account Assignment for Technical Object", type text}, {"Maintenance Item", type text}, {"Maintenance Plan", type text}, {"Priority", type text}}),

    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type1", "Text Before Delimiter", each Text.BeforeDelimiter([Functional Location], "-", 2), type text),

    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", "FLOC LVL 3"}}),

    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Order Number", Order.Descending}})

in

    #"Sorted Rows"

Hi dheslin ,

When you say - "Why would power querry within power bi not identify all the rows of data"; I understand that you are trying to check all the rows in data in Power Query window.

I must mention here that Power Query editor doesn't load every single row from you data-source, as it shows few rows.

In order to check if the data is loaded completly in Power BI, check the data under Data section as shown below:

Can you confirm that here you see the right data?

Thanks,

Pragati

Hi dheslin ,

Where do you want to achieve automatic refresh? Schedule refresh can be turned on on Power BI Service, refer to Configure scheduled refresh - Power BI | Microsoft Docs.

Automatic page refresh is enabled in Power BI Desktop, but there are a few things to keep in mind when you use automatic page refresh in Power BI Desktop or in the Power BI service:

• Import storage mode is not supported for automatic page refresh. You can only refresh manually.
• Composite models that have at least one DirectQuery data source are supported.
• You can only have one change detection measure per dataset.
• There can only be a maximum of 10 models with change detection measure in a Power BI tenant.

Best Regards,

Stephen Tao

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

Did you resolve this? Do you know the version of Excel the file is saved as? I have the same issue. I receive an email each morning with an excel file from Cognos and use Power Automate to save it in a SharePoint folder. If I refresh (from desktop or server) it only reads the first 45K lines of the file. If I first open and resave the Excel file, and then refresh, it reads the entire file in. I'm not sure if the issue is the version of Excel Cognos saves the file as or if it is something with Power Automate or SharePoint Online.  

I'm experiencing the EXACT same issue: Alteryx workflow appends data to an existing Excel spreadsheet, but PBI Power Query fails to recognize the newly appended data unless maunally sorting the Excel spreadsheet first.

Did you every find resolution for this? Any feedback would be GREATLY appreciated, thanks.