Forum Discussion
Missing data when loading files from a folder
Hi ripstaur
Your query steps may be inadvertently excluding some rows of data. The query editor only previews a max of 1000 rows. If there's data beyond that it can get left out of your steps exclude it. What rows are missing? What's the data in them? Check your steps to see what they are doing and make sure they don't exclude anything.
For testing purposes, you can also try creating a separate query for each file (rather than using the From Folder connector), load the file and then check if all your expected data is there. At least that may give you some pointers as to what rows are missing,and indicate why this is happening.
Not sure what you mean by I cannot convert the individual worksheets to tables, because I need the dynamic headers to make the user inputs work correctly - it's better to work with Excel tables. Maybe you could create the Excel tables from the ranges where this data is being input?
Regards
Phil
Hi ripstaur
Your query steps may be inadvertently excluding some rows of data. The query editor only previews a max of 1000 rows. If there's data beyond that it can get left out of your steps exclude it. What rows are missing? What's the data in them? Check your steps to see what they are doing and make sure they don't exclude anything.
For testing purposes, you can also try creating a separate query for each file (rather than using the From Folder connector), load the file and then check if all your expected data is there. At least that may give you some pointers as to what rows are missing,and indicate why this is happening.
Not sure what you mean by I cannot convert the individual worksheets to tables, because I need the dynamic headers to make the user inputs work correctly - it's better to work with Excel tables. Maybe you could create the Excel tables from the ranges where this data is being input?
Regards
Phil
I recently experienced this when using the From Folder process in Excel and imagine the same would occur in Power BI when trying to consolidate Excel files where the first few rows contain non-column header information. In my case, the first two rows of data contained a report name and other ancillary information which sounds similar to your own. The third row contained column headers with data following thereafter. In my case, only the first column generated null values for all files other than the Sample File and the ancillary info in the first two rows was in the first column; this is a clue to the issue.
If the files were saved as CSV, the Folder process worked with no issues. If the content from the first two rows were removed where the first available row with data is the column headings, that resolved the issue as well. However, I did not want to need to manually touch any of the files in order for the process to work. It occurred to me that every time I have used the from Folder process with Excel files, the files always had the first-row containing column headers. Again, another clue. Here is what I discovered.
In the Transform Sample File associated with your query, check to see if it has a Promoted Headers step at the end as seen in the second pic.
My guess is you probably do which is what is creating this issue.
It seems that Power Query is expecting the first row of data in Excel to contain column headings. This works most of the time, however, when the first few rows contains ancillary information it promotes something that is not a usable column header. For this reason in my case, it explains why the Sample File had values in the first column but none of the subsequent files did because to do so, they would need their first row to match the promoted header shown above.
Therefore, remove the Promoted Headers step in the Transform Sample File. This will leave you with the first column being Column1 which can then align correctly with all other files Column1.
When you remove Promoted Headers, your query may now generate an error like below.
Now, simply remove the Changed Type step that is looking for the previously promoted Column1 or remove the portion of the formula that is looking for the column that is no longer there.
In retrospect, if there had been other non-header information on row 1 in columns 2, 3, etc., we could expect null values to be present in all those columns as well for all files except the sample file.
Hope this helps!
Sterling @ Ember Analytics