Automating Data Import to Excel with Power Query

Introduction

• Using Power Query to automate importing email attachments to Excel
  • Goal: Automate the process so that new data is incorporated into Excel by simply refreshing
  • Scenarios:
    • Replace old data with new data
    • Retain historical data and add new information

Setting Up

• Download Power Query M code (link provided in the video description)
  • Adjust the M code for your email and folder paths
  • Three phases in the process:
    1. Connecting to the Exchange Server
    2. Extracting data from attachments
    3. Optional data cleanup

Scenario 1: Replace Old Data with New Data

1. Create an Email Folder: Monthly Report Data
   • Stores emails with file attachments
   • Ensures only relevant emails are scanned
2. Excel Setup
   • Go to Data > Get Data > From Online Services > Microsoft Exchange
   • Authenticate with email and possibly password
   • Select Mail > Transform Data
     • Filter for the folder “Monthly Report Data”
     • Filter Has Attachments for True
     • Sort by DateTimeReceived in descending order
     • Keep top row only
     • Remove unnecessary columns
     • Expand Attachment Content
     • Use CSV.Document function to extract data

Scenario 2: Retain Historical Data and Add New Information

1. Excel Setup
   • Similar initial steps to Scenario 1
   • Do NOT filter by date or keep only top rows
   • Expand and extract data from all attachments
   • No deletion of historical data
2. Data Merging
   • Combine data from all emails (e.g., January, February, March)

Automation and Efficiency

• Efficient Email Handling: Create Outlook Rules for automatic email sorting
• Excel Automation: Set queries to auto-refresh upon opening or at regular intervals

Summary

• Power Query can handle tedious tasks of data import effectively
• Enhances focus on crucial tasks by automating smaller tasks
• User feedback and experience sharing encouraged

Conclusion

• Power Query is powerful for automating data imports to Excel
• Streamlining data reporting process to save time and effort
• Viewer engagement through comments and suggestions