Power Query not pulling in all the data.
I am attempting to pull in data from ~25 macro enabled excel workbooks which are located on an office 365 share point site via a data query from folder. I have the share point folders synced and the files are all visible. There is one tab in each of the 25 files that I am pulling into single excel file via the power query. The tab and column headers are all named the same.
Everything appears fine at first, I can transform all the files, combine them using just the tabs in question and I get a nice export. But when I did a pivot off the queried data, I noticed stuff was missing. So I opened the source file, and confirmed data was there (it was).
The missing data isn’t consistent. As in column F for example is populated in all 25 workbooks, but on 3 of them i the query, that data is “null” while it comes across fine in the other 22.
This is when I try to do a power query “by folder”. So to test, instead of by folder, I did the 3 files that were generating null in the query “by file” instead. So just querying off one file instead of a folder worth. And in that scenario, all the data came through perfect. The column F data that would null in the by folder query worked fine in the by file query.
There is something occurring in the folder query that is causing data that is in the source files to null out, but if I query the file directly it works. It is highly preferable to have all data in a single refreshable tab than querying 25 workbooks directly into 25 tabs.
Any insight to what would cause the data loss?