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

[dheslin]

I have two excel files that are generated from an Alteryx workflow that obtains information from SAP. These two files are the only data source for a Power BI build. The refresh of these files happens daily before power bi refreshes the data for the reports. What i have noticed is that power bi is not displaying the same number of rows as the data source.  If i simply change the sort order of one column in the orginal file and save it then power bi will then magically see all the rows of data. I cannot manualy carry this out every day as the process is meant to be automatic. Why would power querry within power bi not identify all the rows of data unless I open the orginal data source and then effectively shuffle the rows and save. 

Anyone out there have a similar issue that is corrected by simply opening the original file and saving it???? I cannot see a solution to these missing rows suddenly becoming visable after only carrying out a sort in decending order....

Any help would be appreciated please. 🙂

[tarichar1031]

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. 

[Philip_Westover]

I had the same issue with an Alteryx append and was able to resolve it with the below thread..  specifically the comment from DavidSkaife about changing the code in the Source step.  That code was easy to find if just importing one file but I was doing a Combine Files so then the code is in the Transform File function in the Helper Queries area. 

Alteryx Community: Alteryx, Power BI, or Excel Issue

[mserraManxa]

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!

[dcluser]

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.  

[v-stephen-msft]

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.

[Pragati11]

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

[dheslin]

Hi Yes if i use the data view i can see the total rows. 

For example here is todays values after I have resorted the excel file outside of Power BI. I will check tomorrow and put the value i find here v's the value that is in the excel file.  It will be different, i will then perform a sort and save on the file and show the updated table data screen shot so you can see my issue.

[Pragati11]

Hi @dheslin ,

You really don't need a sort to see number of rows in your data under the DATA section in Power BI. It should always show you total rows that Power BI has loaded from the data source.

Thanks,

Pragati

[dheslin]

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"

[KNP]

Thanks for the extra detail.

Are you noticing this behaviour in the service and Power BI Desktop?

How big are the two files (row count and file size)?

I've never heard of the behaviour you're describing and you certainly shouldn't have to do anything to the files to cause it to update correctly.

This is going to sound like a stupid question but does the report update correctly if you click the 'Refresh' button on the report?

[dheslin]

Good morning the file sizes are around 104,000 KB of data or less and the row counts are currently around 300000 each (2 files) both displaying the same problem.

Once the power bi report scheduled refresh has occured the page should show the correct lines from the files but it does not. When i press the refresh button the values don't change until i have sorted and saved the excel original files and refreshed the data model.  Very odd. The refresh button does work though as you can see the visual update once i have refreshed the data model.

[KNP]

That is odd. I'm intrigued.

Is there anyway you could share the files with me? Masking any sensitive data obviously.

I realise that may be a difficult task. I could create similar size files but I don't think I'm going to run into that problem.

How many columns in each file?

[KNP]

I've created a file with 52 columns and over 320,000 rows and it updates no problem. 

Using the personal mode gateway but I doubt that would have a material impact. 

Even with that many columns and rows the file is only sitting at 70,152 KB, but that is likely down to data types. I'm certain you're not anywhere near any file limitations.

Do all of your updates in the Power BI Service always show success?

[dheslin]

Hi, So unfortunately I cannot share the information with you. in the two files there are approx 50 columns pretty much as you described in your test. I have no issues with other file updates ,only this one for some reason. I was thinking it may be related to the excel file being flat page of data rather than an table but powr BI promotes the headers anyway and turns it into a table so i cant see that being an issue? I'm really lost as to why.... Any other advice?

[KNP]

• When you have it open in Power BI Desktop and click 'refresh' does it refresh as expected?
• Are there relationships between the two tables configured in the data model?
• Is the data and report visuals in the same PBIX file?
• Can you confirm the Gateway Refresh history shows success every time?

You could try turning on the 'Additional logging' in the gateway to see if that provides any useful info.

Really, at this point, any additional info you can provide may be useful.

I'll see if I can summon anyone else who may know.

[KNP]

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.