How to handle Quarterly Excel and CSV Files

[gmat]

Hi everyone,

I need some guidance on the best approach to manage and visualize my data in Power BI. Here’s the situation:

1. Data Sources: I have multiple Excel and CSV files for each quarter, spanning from Q1 2019 to Q1 2024. Each quarter has:

   • An additions file (Excel) – used to track additions to our units.
   • A flat file (CSV) – represents the current mix of units at that point in time.

2. Data Characteristics:

   • There are no dates within the data itself.
   • The data format is consistent across quarters.
   • These files are large and come from an external source, so they don't live in a database.

3. Objective:

   • Additions File: Build a historical view of unit additions from 2019 onwards.
   • Flat File: Track the current mix of units for each quarter.

Challenges:

• Data Integration: Unsure about the best way to integrate these files. Should I import each individual file separately? Will Power BI handle this large dataset efficiently?
• Historical View: How can I create a matrix with dates as columns to show a historical view when the data resides in different tables?

My Current Thoughts:

• Importing each file individually might be time-consuming and potentially inefficient for large datasets.
• Combining data from multiple files into a single table for easier analysis and visualization.

Questions:

1. What is the best method to handle and integrate these files in Power BI?
2. How can I create a matrix that shows historical data across different quarters when the data is spread across multiple tables?

[lbendlin]

create two queries.  One to collect the list of the CSV files, and then to combine their content.  The other to do the same for the Excel files.

Resist the urge to try and combine these two queries in Power Query.  Let the data model / DAX do the work for you.

[gmat]

Can you give me an example of how to do this? Basically you are saying I need to write a query to grab the data from the individual excel files?

[lbendlin]

yes, sort of a template.  Describe the extraction process for one file, and then apply to all files.  Similar to what the "Combine binaries"  feature does but without all the extra baggage.