Skip to main content

Notifications

Power Automate - Building Flows
Answered

How to read specific columns of excel in power automate

(1) ShareShare
ReportReport
Posted on by 75


I have an excel file with key columns and also 365 columns with dates as header in different columns for whole year, My requirement is to read key columns and also the date column which equals to current day only and send email with content of that particular date columns 

 

For example when Power Automate workflow runs on 10th Jan, it should read the date under the column - 10th Jan only . Not other columns like 9th or 11th Jan. Next day it should read 11th Jan and so on.

 

Is it possible to automate such scenarios in power automate?. How can I build flow to read different columns in excel depending on the date it is triggered?

 

  • Suggested answer
    takolota1 Profile Picture
    takolota1 4,779 on at
    How to read specific columns of excel in power automate
    You can also use this template Azure Function to pull data from any Excel sheet without the need for table formatting...

    https://community.powerplatform.com/galleries/gallery-posts/?postid=eea06380-8483-ef11-ac21-7c1e5201b42f

    Or if you need something without premium connectors, then you can use these Office Script templates…
  • Suggested answer
    takolota1 Profile Picture
    takolota1 4,779 on at
    How to read specific columns of excel in power automate
    You can also use this template Azure Function to pull data from any Excel sheet without the need for table formatting...
    https://community.powerplatform.com/galleries/gallery-posts/?postid=eea06380-8483-ef11-ac21-7c1e5201b42f
  • Sivaperumal31 Profile Picture
    Sivaperumal31 75 on at
    Re: How to read specific columns of excel in power automate

    Yes, thank you. 

    Just tested right after replying to the thread, it does not pick up data outside table. 

  • grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: How to read specific columns of excel in power automate

    If that extra information is not part of the actual Table, then it won't include that information when it brings in the data to Power Automate.

  • Sivaperumal31 Profile Picture
    Sivaperumal31 75 on at
    Re: How to read specific columns of excel in power automate

    Thanks a lot, it's reading the columns dynamically now.

    I am using the Table from excel to read, so above solution worked perfectly.  

     

    Also, how we can filter out certain rows, in case the excel contains some title details above the actual table data like in the example attached.

    Unwanted RowsUnwanted Rows 

     

     

     

  • Verified answer
    grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: How to read specific columns of excel in power automate

    Hopefully this will get what you're looking for. Note that I had to convert your Excel data to an Excel Table. You can then reapply the formatting to the Table as required. Let me know if this is not possible to set as a Table but would definitely make it harder. Also, note that I didn't add anything relating to sending emails - just focused on getting the appropriate data from Excel.

     

    For this example, I'm using the following Excel Table (only includes a few dates for testing purposes).

    grantjenkins_0-1675759131319.png

     

    See full flow below. I'll go into each of the actions.

    grantjenkins_1-1675759181785.png

     

    Recurrence is set to run once a day. Note sure what you have here.

    grantjenkins_2-1675759271024.png

     

    List rows present in a table will retrieve all the data from your Excel Table. We won't be able to filter the data at this point.

    grantjenkins_3-1675759319949.png

     

    Filter array then filters on the rows where the current date column is equal to No (reports that haven't been completed for the current date). The expression used is below. It retrieves the value from the column titled the current date (Jun-21 for example).

    item()?[formatDateTime(utcNow(), 'MMM-dd')]

    grantjenkins_4-1675759885657.png

     

    Select uses the output from Filter array and only maps the relevant fields we want. The expressions are below:

    item()?['ID']
    item()?['Report']
    item()?['Frequency']
    item()?['Due on']
    item()?['1st Owner']

    grantjenkins_5-1675759980192.png

     

    After running the flow, we would get only the items where today's date value is No. We could then loop through each of these items and send an email with the relevant data.

    [
     {
     "ID": "2",
     "Report": "Report 2",
     "Frequency": "Daily",
     "Due on": "Daily",
     "1st Owner": "A1"
     },
     {
     "ID": "4",
     "Report": "Report 4",
     "Frequency": "Daily",
     "Due on": "Daily",
     "1st Owner": "A1"
     }
    ]

     

    grantjenkins_6-1675760059642.png


    ----------------------------------------------------------------------
    If I've answered your question, please mark the post as Solved.
    If you like my response, please consider giving it a Thumbs Up.

  • Sivaperumal31 Profile Picture
    Sivaperumal31 75 on at
    Re: How to read specific columns of excel in power automate

    Hello, 

     

    Thanks for the reply. Attached the sample screenshot. 

    Basically, the idea is to send the mail form power automate to Report owners if their Report is not refreshed as a reminder at the end of the day. 

     

    I do not need all the columns. The Mail need to state what report is not refreshed along with frequency. 

     

     

     

     

  • grantjenkins Profile Picture
    grantjenkins 11,057 on at
    Re: How to read specific columns of excel in power automate

    Are you able to show a screenshot of some column headers within your Excel file? Definitely possible - just wanted to know the exact format and whether it's consistent across all your column headers.

     

    Also did you want all the rows from the Excel file? And what did you want to do with the data?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard