Power Platform Community Forum Thread Details

I currently get reports on reports sent weekly to me on the stock volume levels of certain products and their product codes like so

My consolidated excel sheet has all of our product codes and names in column a/b and every column after it is the date the weekly report comes:

I wish for the respective product stock levels each week to automatically populate this sheet under their respective date columns.

What's the best way to approach this? Im assuming the trigger is when email under x subject/sender comes and retrieve attachment but not sure how to do the consolidation part.

Thanks

Categories:

Using flows

Building flows

You’re thinking in the right direction (email trigger + attachment) — now the key is how to map and consolidate the data correctly.

Here’s the best practical approach using Microsoft Power Automate + Microsoft Excel

Goal (Your scenario)

Each week:

You receive an Excel file (stock report)
You want to:

Match Product Code
Update Stock value
Place it under the correct date column

Recommended Solution (Step-by-step)
Step 1: Standardize both files

Source (Weekly file)

Must have:

Product Code
Product Name
Stock

Convert it into an Excel Table (important)

Target (Consolidated file)

Structure like:

Product Code | Product Name | 01-Jan | 08-Jan | 15-Jan ...

Also convert into Excel Table

Step 2: Build Power Automate Flow

Trigger

When a new email arrives (V3)
Filter:

Subject = your report subject
Has attachment = Yes

Step 1: Get attachment
Use:

Get Attachment
Create file (temporary in SharePoint/OneDrive)

Step 2: Read Excel data
Action:

List rows present in a table

Step 3: Loop and update consolidated file
Apply to each (row from weekly file)

Inside loop:

Find matching product

Use:

List rows (from consolidated file) OR better:
Get row using key (Product Code)

Identify current week column

Example:

formatDateTime(utcNow(),'dd-MMM')

This becomes your column name (e.g., 22-Mar)

Update row

Use:

Update a row

Map:

Product Code → match
Date column → Stock value

Key Challenge (Important)

Excel connector needs:

Static column names

So:

Best practice

Pre-create date columns OR

Use:

Option A (Recommended)
Keep table like:

Product Code | Date | Stock

Then append rows instead of updating columns

Option B (Your current structure)

Pre-create weekly columns
OR use Office Script (advanced)

Thanks
Manish

Visit my blog : https://powerappbuilder.blogspot.com/

Community site session details

How to have (stock volume) weekly reports auto-consolidate into a single excel sheet

Helpful resources

News and Announcements

Quick Links

Introducing the 2026 Season 1 community Super Users

Kudos to our 2025 Community Spotlight Honorees

Congratulations to the April Top 10 Community Leaders!

Leaderboard > Power Automate

Featured topics

Product updates

Community site session details

How to have (stock volume) weekly reports auto-consolidate into a single excel sheet

Helpful resources

News and Announcements

Quick Links

Introducing the 2026 Season 1 community Super Users

Kudos to our 2025 Community Spotlight Honorees

Congratulations to the April Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate

Featured topics

Product updates