Power Automate

Answered

Switch action problem

(2) Share

Report

Posted on by KS-29050310-0

106

Hi, everybody , Could you help me with the question below?

Question:

My test failed, and I would like to know the reason and how to fix it. I suspect the problem might be related to the Switch action.

Background:

I am using Power Automate to transfer project data from "Sheet1" of an Excel file stored in a SharePoint document library into a SharePoint list. There is only one file in the folder, and the data in this file is overwritten every month by an ETL process. Each month, I need Power Automate to map the data from "Sheet1" to different columns in the SharePoint list. For example, when the Excel data is for January, the values from the [revenue] column in "Sheet1" should be placed in the [January_revenue] column of the SharePoint list. When a new month's data replaces it (e.g., February), the [revenue] values should be stored in [February_revenue], and so on.

What I did :

I added another sheet, "Sheet2," to the Excel file and always place the current update month in cell B2 of "Sheet2" (see screenshot below). I use the "Get a row" action to retrieve the value from this cell. When the value in B2 is "202506," Power Automate directs the data to the columns for June (e.g., [June_revenue]) in the SharePoint list, and similarly for other months.

Each month, I need to update the project data, and I may also need to add new projects. When a new project starts, I insert new rows into the SharePoint list from "Sheet1." To achieve this, I use a control action and set the project code as a unique key to determine if the project is new or already exists. If the project already exists in the SharePoint list, I update its data; if not, I add a new record.

The table in sheet1 names table2

The table in sheet2 names table1

Translations

Sorry that the screenshot is in Japanese. I’ve provided the English names of the actions below so you can compare them with those shown in the screenshot. For your reference, I’ve included the Japanese translations directly after each action’s English name.
When a file is created or modified (properties only)（ファイルが作成または変更されたとき (プロパティのみ)）→get a row（実績月の取得）→list rows present in a table（表内に存在する行を一覧表示）→apply to each→　get items（複数の項目の取得）→control （新規既存製造コードの判定）

∟　true → Switch（スイッチ）→different month→[create item]

∟　false →Switch（スイッチ1）→different month→ [update item]

分 means minute

秒 means second

Here is the actions under switch action. Because there isn't any data in the sharepoint list, so the answer of the control action will be always true.

月 mean month

6月 June, 7月mean July and so on.

項目の作成 means create item

The parameter in actions↓

trigger : When a file is created or modified (properties only) →I chose a file in a sharepoint doc library

get a row: Key Column→No. Key value →1　

list rows present in a table（get the data from excel）: 　table→table2 Filter Query → project code ne ''

datetime format→ ISO 8601

for getting excel data more than 256 rows, I have turned the Pagination on and set the threshold to 5000

apply to each : outputs('list rows present in a table')?['body/value']

get items（get the data from sharepoint）: filter query → Title eq 'items('Apply_to_each')?['オーダー番号_前月締め']'　top count→1

control : condition expression →empty(outputs('get items')?[''body/value']) is equal to true

∟　true → Switch →create item

∟　false → Switch → update item

Switch：On → outputs('get a row')?['body/month']

June（6月）: Equals→ 202506

July（7月）: Equals→ 202507

August（8月）: Equals→ 202508

and so on.

sample of sheet 1

sample of sheet2

Some error caputures

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

Michael E. Gernaey 53,442 Super User 2025 Season 2 on at

Like (2)

Report

Hi @KS-29050310-0

Firstly apologies my Japanese is not great, if it was french or german or other language it would help me more to help you so I will ask lots of questions

1. Firstly, you should change your condition from checking Empty to Length = 0 so use the length(body/valueHere) instead of empty

2. Here

Can you please share the rest of what it says as it may give me what I need

OK, so I am not 100% clear on how you are telling me the issue, but let me try to replay what you said and what I think you want.

Questions / thoughts

a) When does this run? Every day or start of the month or end of the month? I am asking because you said it is overwritten every month.

My suggestion instead of having a Sheet 2, is to simply grab the Month value from the utcNow() expression. This will make it cleaner. You can even check the last modified date of the file, and use that, convert it just to a Date, then use the Month expression to grab the month, and then you can use that to filter in the primary Sheet(table)

b) While I see it seemingly triggering every Switch Case, that is definitely a bug. If you are only filtering for say February, I would NOT expect it to hit any Case except Febuary. If there is NO data, then 1) You should not even go to the Switch 2) and even if you do, it should only hit the default Case NOT the others.

So we need to better understand why its triggering them all, which is wrong.

That being said, if you are going to be comparing dates, you should specifically use formatDateTime to get rid of the Time Part altogether and just have the Date to make it easier

You mentioned that you believe the issue is in the switch, but you also mentioned there is no data, so I would expect you to always go to the Create Side (No) from your condition, not the Update Side.

So can you clarify if the data is actually taking you to the correct side? Yes/No or is it always going to one side only? Which would be ok... I guess if initially you only get New ROws for the month and updates happen in the next month or months after, but is that the case? That the updated file only has updates for PREVIOUS months and only NEW rows for the current?

Was this reply helpful? Yes No
KS-29050310-0 106 on at

Like (0)

Report

@Michael E. Gernaey
Thank you for your comment.

I tried utcNow() action. It works! So, I think maybe the problem is [get a row] action.

I deleted the [get a row] action, and wrote the expression in the switch "On" box like this

utcNow('yyyy-MM')

And made one case "equals" 2025-08

1. Regarding the "empty" advice, I tested. But it didn’t work, I got the same error as the one I showed you in the question.

2.It like this

ActionConditionFailed

The execution of template action '項目の作成' failed: an unexpected exception encountered when evaluating branching condition.

項目の作成 means [create item] action.

Questions / thoughts

a) The power auto flow runs each time I overwrite the data in Sheet1 using ETL. You can check it from the trigger. The data in Sheet1 is typically overwritten about once a month by ETL, but this process does not happen automatically. The overall architecture is as follows: ETL→sheet1→sharepoint list

Because the ETL process is not automated, it is difficult to ensure that the data will be overwritten and updated at a specific time or on a specific day. Therefore, it is a little difficult to use utcNow() . But thank you for your advice—it will be helpful in the future when the ETL process becomes automated.

b)It doesn't trigger all the switch cases. Like you said, it will only filter for a single month. There are 12 switch cases, each representing one of the 12 months. Different switch cases have different values in their "Equals" box. Only the switch case whose value matches the number retrieved from cell B2 in Sheet2 will be executed. For example, if the value in cell B2 is 202506(here I set the datatype to number), only the switch case with 202506 in the "equals" box will be executed.

"If there is NO data, then 1) You should not even go to the Switch 2) and even if you do, it should only hit the default Case NOT the others."

This sentence confused me a little. I believe I only mentioned that there is no data in the SharePoint list.

”So can you clarify if the data is actually taking you to the correct side?”

you mean if [switch] action take me to the correct switch case?

If you mean the [control] action, from the run history I have already known it would take me to the correct side（true）

You can see this in the screenshot: there is a grey mark next to Switch 1, which indicates that the action was skipped.

When the project was new, The answer of the control action would be [true].

” I guess if initially you only get New ROws for the month and updates happen in the next month or months after, but is that the case? ”

Yes, that's correct. The data in Sheet1 will contain both ongoing project data and new project data at the same time.

Was this reply helpful? Yes No
Verified answer

KS-29050310-0 106 on at

Like (0)

Report

I found the reason—it was a datatype issue.
So, I changed the datatype in the switch case "equal" box to a string (e.g., "202506" in double quotes), and it worked successfully.

Was this reply helpful? Yes No