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
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