PowerAutomate Desktop not recognizing data table

Question

I've been tasked with automating reminders of when to calibrate instruments in my lab. We have an existing spreadsheet containing the dates, but it's a legacy file that needs to be updated to work properly. I have been experimenting with Power Automate Desktop to determine the best format for the new spreadsheet that we'll use going forward, and which will be read by the desktop flow each day.

The goals are thus:

1. Maintain a spreadsheet that contains information such as the equipment ID, manufacturer, location of instrument, last calibration date, and calibration expiration date
2. Read the spreadsheet column that contains the expiration date
3. Send out emails at 60 days ahead of time, 30 days, 15, 5 days before the expiration
   1. This email should contain not only the expiration date, but also the ID, location, manufacturer, etc., which I have split into different columns in my example file. Ideally, the automated email being sent should be formatted similar to: "Hello, this is an automated reminder that [equipment ID], the [manufacturer] [Instrument type] has a calibration certificate or verification period that expires on [expiration date]. This piece of equipment is located in [location]. The instructions to recalibrate or reverify are [instructions]. The previous entity that performed this work was [metrology lab]."
   2. In pursuit of this, each of these bracketed items are the titles of different columns in the excel file.

I am currently pursuing this method:

1. Get current date and time
2. Define variables for 60 days from now, 30 days from now, and so on
3. Open target excel file as ExcelInstance
   1. get names of all worksheets in excel file and list into SheetNames
4. For each PageName in SheetNames,
   1. Activate worksheet PageName of ExcelInstance
   2. Get first free row of Excel Worksheet and store as FirstFreeRowOnColumn
   3. LastFilledRow = Int(FirstFreeRowOnColumn) - 1
   4. Read A1 through Indigo:Int(LastFilledRow) and store as ExcelData

This is where the code stops working. The issues I can determine are thus:

1. I cannot find a reliable source of information for how to reference a column in a Datatable.
   1. It appears that a valid way to reference a column in a datatable was Table.Column, or Table.'Column Name if spaces' ; When the program recognizes that I am trying to access a field, it tells me that the "." operator has been deprecated and to use ShowColumns which DOES NOT WORK, informing me each time that I have given it a Deferred Type, when I am using the exact following format: ShowColumns(ExcelData, 'Cal Due'), or if I use double quotations, that it is an invalid argument. I have double and triple checked that that is the exact name of the column I am trying to print into word.
   2. When I use Filter(ExcelData, ...), it throws the pictured error, which is particularly infuriating. The exact format I currently have reads =Filter(ExcelData, DateTime(ThisRecord.'Cal Date')=TodayPlus60) ; I am trying to create a new table called FilteredDates60 containing the entire rows of all of the records that those dates match.

Things I have already tried:

1. I have already tried operating on the excel file itself. However, that was removing rows entirely, which is unacceptable behavior.
2. I have tried "printing" the column to a word document, which worked at one point, and now I cannot reproduce it.
3. The filter function will not accept my valid datatable as an argument. If I get one thing out of posting this, I hope it is how to refer to things in datatables.

Answer 1

Hi James Fish,

Thank you for reaching out to us on the Microsoft Q&A forum.

This topic is currently not supported in the Q&A forums.

I recommend initiating a new discussion through the Power Automate Community platform
Moderators are readily available there to assist you and provide guidance.

Please don't forget to Accept helpful answer and close this thread.