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
Phil,
Thanks for giving me some things to check..Your second paragraph helped a lot. Here's what ended up somehow working: I went in and loaded each file separately in PBI. I learned a couple of things:
1. I had some spurious hidden data in one of them (this seems to have been what kept the file load from working). So I went back into each file and deleted 3 (blank) rows from beneath the array I wanted to use.
2. If you have an array where some columns contain no data (only a header), PBI won't recognize your headers as headers.
After deleting those rows in each file, I went back to PBI and used the folder option. This time it loaded. I just had to click on "use first row as headers" to promote the header, then everything worked fine.
BTW, what I meant about the table is this: The data files in the folder are results from a questionnairre, which is sent out to respondents as an Excel workbook. They provide their answers on one sheet, and then the answers are compiled/simplified in another worksheet. The completed questionnaire workbooks are kept in the folder, and the compilation worksheets are what I select when I load the data from the folder. Each compilation sheet contains an array of 12 columns and 11 rows. This particular questionnaire is sent to up to 10 individuals.
The headers in the compilation worksheet come from formulas which bring product model names in from another sheet. I tried inserting a table, but I get an error telling me that the headers have to be text and cannot be formulas.
Best regards,
Rip
Hi ripstaur
Yes Excel table headers must be text. But if you were using tables you'd have avoided this particular issue with hidden data/blank rows.
You can create the tables with generic column names and then after import to PBI, rename the columns.
Regards
Phil