Combine Data files in Excel Data Model for Queries

[laurahutch]

Hi All! I am new to the forum and BI, but this is what I'm working with:

1. We receive/download approx 23 files per day from a vendor share site - combo of csv/excel & pdf reports (though naming conventions are NOT consistent)
2. We have a daily tracking sheet of numerous rows we maintain and currently enter manually based off 6 or so reports.
3. In the tracker, I have identified the report & column header name needed to populate each row.

Here are my questions (in order I hope):
1. Is there an easy way to upload approx 13 csv files to Excel BI, or would I continue to run a separate macro that converts/saves out an XLS version, so it can be uploaded as a folder?
2. What is the best method to upload? I've seen varying methods, but want to know what would be best with my Access background.
3. There are no primary keys, so how would I create relationships between the various reports?
3. The blissful end goal would be to upload all the data and create our own report that simply consolidates/summarizes all the reports we are sent without needing to go back and forth between reports to get a # that is only listed in one report (i.e. the totals for all column headers.)

I have prior experience with Access, but with the amount of data this requires, I know Access just won't be a LT solution.

Thank you SO much for any help/guidance!!

 

[Matt Allington]

Your questions are very broad and I’m. It sure the answers will help you apart from knowing you should pursue this approach
1. Yes
2. File combine. https://exceleratorbi.com.au/combine-csv-files-power-query/
3. Create one or more dimension tables https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
3. Yep, that’s what it’s all about. Completely doable.