Forum Discussion

dheslin's avatar
dheslin
Regular Visitor
4 years ago

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

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...
Need Help
dheslin's avatar
dheslin
Regular Visitor
4 years ago

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's avatar
KNP
Super User
4 years ago

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's avatar
    KNP
    Super User
    4 years ago

    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's avatar
      dheslin
      Regular Visitor
      4 years ago

      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's avatar
        KNP
        Super User
        4 years ago
        • 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.