Answered

filter with the different item (columns) of the excel array depending on the year at that moment

(0) Share

Report

Posted on by James_Chu

I try to use power automate to "filter array" action to filter the excel list row by different columns depending on the year at that moment. But I couldn't add any dynamic variable into the item() function. Please give me some suggestions or alternatives. Thanks!

More detail about my scenario is following.

This year (2022), the filter array condition would be ：
@And(not(equals(item()?['2022委託繳費編號'], '#N/A')),not(equals(item()?['2022委託繳費編號'],'')))

Next year (2023), the filter array can automate change the condition to：
@And(not(equals(item()?['2023委託繳費編號'], '#N/A')),not(equals(item()?['2023委託繳費編號'],'')))

In 2024, the filter array can automate change the condition to：
@And(not(equals(item()?['2024委託繳費編號'], '#N/A')),not(equals(item()?['2024委託繳費編號'],'')))

and it can be keep update following the regulary forever.

@And(not(equals(item()?['(That Year)委託繳費編號'], '#N/A')),not(equals(item()?['(That Year)委託繳費編號'],'')))

Only the number change depending on that year. Chinese words wouldn't change.

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@James_Chu ,

Can you not use a switch case for this scenario?

Was this reply helpful? Yes No
James_Chu 16 on at

Like (0)

Report
Copy link

Link copied!

@SudeepGhatakNZ
Thanks for your reply. In my case, it may work in the specific years I have defined. However, it might not work in the long term. Is it possible to make the following regularly work forever without manually updating the switch rule?

@And(not(equals(item()?['(That Year)委託繳費編號'], '#N/A')),not(equals(item()?['(That Year)委託繳費編號'],'')))
*"The year" represent the term 2025 in 2025, 2030 in 2030, etc.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report
Copy link

Link copied!
I've got this working using formatDateTime(utcNow(), 'yyyy') to pull out the current year.

My spreadsheet I'm using for this example is:

In my Filter Array I have the following expression:
@And(not(equals(item()?[concat(formatDateTime(utcNow(), 'yyyy'), '委託繳費編號')], 'NA')),not(equals(item()?[concat(formatDateTime(utcNow(), 'yyyy'), '委託繳費編號')], '')))

This works as expected. The output I would get back based on the spreadsheet is:
[ { "@odata.etag": "", "ItemInternalId": "4390f37f-b89b-4f89-8d1a-1ff131252293", "Name": "Test 01", "2021委託繳費編號": "Test", "2022委託繳費編號": "Test" }, { "@odata.etag": "", "ItemInternalId": "fdf9827b-46ae-468a-b909-5ea12a1ff86e", "Name": "Test 03", "2021委託繳費編號": "NA", "2022委託繳費編號": "Test" }, { "@odata.etag": "", "ItemInternalId": "02f9dcd5-e12f-4cb6-91eb-e55af97803e0", "Name": "Test 05", "2021委託繳費編號": "Test", "2022委託繳費編號": "Test" } ]

Was this reply helpful? Yes No
James_Chu 16 on at

Like (0)

Report
Copy link

Link copied!

@grantjenkins
It works!! Thanks a lot for your assistance with detailed instructions and validation!

Was this reply helpful? Yes No