cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ripstaur
Helper III
Helper III

Missing data when loading files from a folder

I have used the "Folder" option in "Get Data" with a lot of success before. I am attempting to build a report now that combines several files, all in exactly the same format (I was very careful about this). I have tried using "Load" and "Transform and Load" options, but the same thing happens...the columns all come in, but some of the data is missing.

 

I have tried looking at each file before I load them all, and the data are all there when I look at them individually. However, when I load the entire folder, some of the data is missing again. 

 

Any ideas? One note - I cannot convert the individual worksheets to tables, because I need the dynamic headers to make the user inputs work correctly. 

 

For now, I am just going to append. It's not the best or most efficient way to go, though, so if anyone has run across this problem (and a solution), it would be great! Thanks in advance. 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
ripstaur
Helper III
Helper III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors