Automatically Import Data from Email Links Using Power Query in Excel or Power BI

Have you ever received an email with a link to download a CSV or Excel file, and wished it could just go straight into your Excel sheet or Power BI dashboard?

Good news: it can.

With Power Query, you can connect directly to these file links—whether they’re public or require Microsoft login—and automatically load them into Excel or Power BI. This blog shows how to do it step by step.

Why Use Power Query for Email-Based Downloadable Links? (M-code to skip the theory at the end of post!)

Let’s say a vendor or system emails you weekly with a link like:

Download your data: https://files.example.com/reports/latest-data.xlsx 

or this kind of links that already have downloadable content:
https://deu01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcdn1.litmoseu.com%2F157184%2Freports%2Ff9e14621-e566-40ff-a2ef-2063c7b3096e.csv%3FExpires%3D1744866190%26Signature%3DTn1t13v96-tnuqZ9NPdZdhG7nYn9F6nsQrxNO6D8sQPPOAEgyLXAM5fbHE4fKeA8vXOkD-b1EllQ6ElTqPEJ1hCvjs1Vviruidc~iEybtK3PgJvr9w2H5-JY9TVmfx35JAAdzKyf34dzSLmqIhcZjc8KFIezWKBQSSO-OKz37X4_%26Key-Pair-Id%3DAPKAIQKC33KEMABEYE6A&data=05%7C02%7Cpowerbi%40ivcevidensia.de%7Ce0a4c1f77e7549d510be08dd7b11a83f%7Cb27a52675eee45749e50c1822c62c2e8%7C0%7C0%7C638802037954673447%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=eg87jfTAGkYwzLQOAEBQ94Fp9SMTDtyFwiirXcpEYUs%3D&reserved=0

Downloadable content from Email on click.

Instead of downloading and importing the file manually every time, Power Query can do it for you.

Benefits:

• Saves time by skipping the manual download

• Keeps your report up-to-date with one click

• Reuses the same data-cleaning steps every time

Step 1: Copy the File Link from the Email

Right-click on the "Download" button or link in the email and choose "Copy link". Make sure the link leads directly to a file like .csv or .xlsx.

💡 If clicking the link downloads a file or opens it in your browser, it will likely work in Power Query.

Step 2: Use Power Query to Connect to the Link (Import)

In Excel:

1. Go to the Data tab.

2. Click Get Data → From Other Sources → From Web.

3. Paste the link and click OK.

In Power BI:

1. Go to Home → Get Data → Web.

2. Paste the link and click OK.

   
   

Step 3: Sign In (If Needed)

If the link is public, you can connect anonymously.

But for protected links (e.g., OneDrive, SharePoint, or Microsoft 365), Power Query will ask for credentials.

✅ Choose Organizational Account and sign in with your Microsoft work/school account.

You only have to sign in once. Excel and Power BI will remember your credentials.

Step 4: Preview and Select the Data from the Email Link

If the file is:

• CSV: Power Query opens it directly.

• Excel: You’ll see a Navigator. Pick the correct sheet or table.

Then click Transform Data to clean it up, or Load to use it as-is.

Step 5: Clean and Load the Data

Inside Power Query, you can:

• Remove blank rows

• Change column types

• Filter or rename columns

Click Close & Load (Excel) or Close & Apply (Power BI) to use the data.

Next time you get an updated file at the same link, just click Refresh!

Extra Tip: What If the Link Requires a Login?

If it’s a Microsoft link (SharePoint, OneDrive), just sign in once using your work account.

You can manage saved credentials in:

• Excel: Data → Get Data → Data Source Settings

• Power BI: File → Options and Settings → Data Source Settings

Power Query can’t handle links behind interactive logins or expiring one-time links. Ask the sender for a permanent link if possible.

Refreshing the Data

• In Excel, right-click the table → Refresh.

• In Power BI, click Refresh on the Home tab.

In Power BI Service, you can also schedule automatic refreshes.

What If the Link Changes Every Time?

Some email systems generate unique links. If that’s the case:

• Use a parameter in Power Query to update the URL manually

• Or use Power Automate to fetch the newest link automatically (advanced)

But if the link always stays the same (like “latest.csv”), Power Query is perfect.

Summary

Using Power Query with downloadable links from emails is a simple way to automate repetitive data tasks. Once set up, you can refresh your data in one click—no more saving and importing files manually.

Whether you work in Excel or Power BI, this technique saves time and keeps your reports up to date. If you receive regular reports by email, try this setup once and enjoy the automation!

Power Query M-code to spare some time (adjust it to your needs in the Source / Folder / Columns). Keep in mind it is coded for today's date (IsToday)!