PQ Excel different results from Sharepoint Folder than PQ PBI

[Doodle]

I created some queries in PQ in Power BI desktop. The query points to a sharepoint folder of excel files, filters results and then pulls in the data from those filtered excel files.

I then copied and pasted the all the Queries and Query Folders (queries, help queries, paramters, sample query, etc.) from PBI to Excel. Literally everything is exactly the same in terms of query, code and links. 

In total, I copied over 6 query groups.  Of those, 4 worked as expected, but then the 5th and 6th queries returned errors, saying that the table of data could not be found.

In looking into the queries, it seems that PBI queries can find the folder and the files but Power Query in Excel cannot. Please see the snap shot.  

Please let me know how to get Excel PQ to "see" those files. Any ideas are appreciated.

 

[v-frfei-msft]

Hi @Doodle ,

Based on my test,  I cannot reproduce your issue here.  Could you please refresh your data to check again? BTW, I'm using excel 2016.

[Doodle]

Hi @v-frfei-msft ,

Thanks for your reply. I am also on Excel 2016. I have refreshed and the issue still persists.

I think the issue is due to creation date in Sharepoint not being recognized by Excel PQ. Note that I am in Asia and our date format is DD/MM/YYYY. Here is a new example to illustrate:

I have a folder in Sharepoint where I upload weekly reports. In the snapshot here, we can see that there 15 files total, with 2 files that are less than 1 week old based on creation date. In sharepoint, it says created 21 minutes agao and created 6 days ago. (Yellow highlight) There is also one file that was created Aug 27, but modified 19 hours ago.

Now i created a simple query in Excel to see what files would be returned.  The results is that only 13 files are returned.  The two files that have creation dates that say "created xx time ago" do not show. If we look at the modified dates, in Sharepoint the file that was modified "19 hours ago" when appearing in Excel query shows modified date correctly on Aug 24. So, I guess the issue only affects the creation date field.

Is this a known issue that I just discovered for myself? Is there a fix/workaround for it? Please advise.